Format Dates in ISO 8601

Hi,

I'm totally new at SQL Server, I'm sorry if this question looks very simple, but I just don't know how to change the datetime value in a field on my database.

I'm working in vb.net and I have a datetime field in sql server, I can store date values passing this value:

DatePick.Value.ToString("s")

dates are saved fine, but if want to make this query:

Select * from backlog where date1>=isdate('21/02/2006') order by date1 asc

I get all the values from that field, no filter is done. Date are shown like this 2004-05-23 14:25:10.000 in the query.

What do I have to do ? do I have to change the date format of my field to ISO 8601 ? how ? do I use a script or what ?

Please remeber that my sql script should be done from vb.

I really appreciate your help.

George

Hi,

ok, the following query looks strange to me as the "isdate('21/02/2006')" part will always return 1 as the date in the brackets is always a date, the function isdate only returns 0 for a non.date and 1 for a valid date. For a date comparison you should use something like date1>= '02/21/2006' (see the switched month and day for an automatic / implicit date conversion)

I get all the values from that field, no filter is done.

Date are shown like this 2004-05-23 14:25:10.000 in the query.

Thats by design, datetime values include date as well as time, but the application should be able to get the value and only use the datepart of it (if you want to).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

You don't necessarily have to switch the day and month around. This is actually controlled by your language setting (for the login). If you have a language setting that interprets dates as day/month/year, then you can use your original format.

When using dates within SQL Server, it is best to use the ISO Unseperated Date Format which is YYYYMMDD. SQL Server will never misinterpret a date in this format.

The problem with your query is that IsDate returns a 1 or 0. Since IsDate('21/02/2006') probably returns a 1, you are actually selecting all dates greater than Jan 2, 1900 (which is probably all of your records).

I suggest you modify your query to...

Select * from backlog where date1 >= '20060221' order by date1 asc

To determine your language settings, you can run:

sp_configure 'default language'

This will return a config_value which you can use to lookup the actual language when you run:

sp_helplanguage

You should know that the language setting is based on the login.