SQL Date confusion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Date confusion

Author

Topic

Navy1991
Starting Member

1 Post

Posted - 2010-01-25 : 08:18:09

I have a simple SQL statement with a WHERE clause for a date field. I have tried the following statement

WHERE (date >= '12/01/2009' AND date < '12/31/2009')

I am getting a error that reports "Data type mismatch in criteria expression."

The date in the field reflects the following: 12/02/2009 7:36:25 AM

Thanks in advance for any help!

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 08:24:04

Change "string" dates from "12/01/2009" to "20090112". Any other format requires an implicit cast which relies upon the locale setting for the server, which is deeply unreliable.

If you have to present string dates in "dd/mm/yyyy" (or any other) format then use the CONVERT(datetime, 'dd/mm/yyyy', 999) function to explicitly cast them using the correct format. (See SQL documentation for correct values for the "999" parameter)

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-01-25 : 08:54:58

just to add to Kristen's point:

The only 2 ISO standard date strings for SQL SERVER are the two following:

1) Short version 'YYYYMMDD' (what Kristen posted) Example

SELECT CAST('20090101' AS DATETIME)

and the long version if you need the time portion

2) Long Version 'YYYY-MM-DDTHH:MM:SS.<MS><MS><MS>' Example

SELECT CAST('2009-01-01T23:23:23.120' AS DATETIME)

There can be no confusion if you use those formats.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-25 : 08:58:12

If you can't change the input format, look for dateformat option too

Madhivanan

Failing to plan is Planning to fail

X002548
Not Just a Number

15586 Posts

Posted - 2010-01-25 : 13:05:09

Come again?

SELECT ISDATE('12/01/2009')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2010-01-25 : 14:03:07

Yeah, needs citation I agree.

I have looked up the example I was thinking of, and its not IsDate()'s fault. This won't work (in SQL 2000):


DECLARE	@strDate varchar(20)

SELECT	@strDate = '31/31/2010'

SELECT CASE WHEN IsDate(@strDate) = 1 THEN CONVERT(datetime, NULL) ELSE CONVERT(datetime, @strDate) END

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 01:37:15

quote:
Originally posted by X002548

Come again?

SELECT ISDATE('12/01/2009')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

I told you already it is not reliable

select isdate(2000),isdate('2000'),isdate(350000/39)

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2010-01-27 : 03:27:06

Cheers Mate.

Brett: I cite Madhi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources