Problem with Date in Selection Criteria

Hello All,

I am facing strange problem in SQL Server Query Analyzer, I am writing following query:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'

Query failed & gives following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

But if I write same query in this way:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28'
OR
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'


It works fine! Can anybody explain me why it is so???

Many thanks in advance…

Cheers,
Santosh

From BOL:
SQL Server recognizes date and time data enclosed in single quotation marks (') in these formats:

Alphabetic date formats (for example, 'April 15, 1998')


Numeric date formats (for example, '4/15/1998', 'April 15, 1998')


Unseparated string formats (for example, '19981207', 'December 12, 1998')

so when you enter date as '15/4/1998' SQL by default interprets as MMDDYYYY
and since 15 is out of range for months- in your case 28 it will spit out an error like the one you are getting.

Hth
Hiku
------------
Santosh at 4/10/01 1:42:00 PM

Hello All,

I am facing strange problem in SQL Server Query Analyzer, I am writing following query:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'

Query failed & gives following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

But if I write same query in this way:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28'
OR
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'


It works fine! Can anybody explain me why it is so???

Many thanks in advance…

Cheers,
Santosh

if you want to compare numeric values you need to provide a particular format for the datetime column. If you are using a server where language is us_english by default the format to use is : mdy.
You are using: dmy

Try this:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '02/28/2001'

The format is set with "set dateformat", so if you want your query to run '28/02/2001', you will need to set dateformat to : dmy like this:

1> set dateformat "dmy"
2> go

and that's it.



------------
Santosh at 4/10/01 1:42:00 PM

Hello All,

I am facing strange problem in SQL Server Query Analyzer, I am writing following query:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'

Query failed & gives following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

But if I write same query in this way:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28'
OR
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'


It works fine! Can anybody explain me why it is so???

Many thanks in advance…

Cheers,
Santosh

When you use the DD/MM/YYYY format, SQL Server thinks that you're really using the MM/DD/YYYY format. It thinks that you're passing in 28 as a value for the month and you get the error that you've seen.

You can set the date format using SET DATEFORMAT (check the online help for specifics). Alternately, the language setting can affect the date format(s) that SQL Server expects.

Good luck,
-Tom.

------------
Santosh at 4/10/01 1:42:00 PM

Hello All,

I am facing strange problem in SQL Server Query Analyzer, I am writing following query:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'

Query failed & gives following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

But if I write same query in this way:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28'
OR
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'


It works fine! Can anybody explain me why it is so???

Many thanks in advance…

Cheers,
Santosh

The following query will always work not depending on your local settings :

Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '20010228'

If you want to specify the time, use the format 'yyyymmdd hh:mm:ss.nnn'


------------
Santosh at 4/10/01 1:42:00 PM

Hello All,

I am facing strange problem in SQL Server Query Analyzer, I am writing following query:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/02/2001'

Query failed & gives following error:
Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

But if I write same query in this way:
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '2001-02-28'
OR
Select WorkItemKey,DateCreated,DateDue, DateCompleted from WorkItems Where DateCreated > '28/Feb/2001'


It works fine! Can anybody explain me why it is so???

Many thanks in advance…

Cheers,
Santosh