SQLTeam.com | Weblogs | Forums

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value


#1

Hi all, hope in your help.
I have problem to execute query with interval date.

If try this query I don't have problem and the output is correct:

SELECT * FROM dotable
WHERE
	dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);

Instead if try this I have error:

SELECT * FROM dotable
WHERE
	dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);


[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Why?
Can you help me?

Thank you in advance.

Version Microsoft SQL Server 2008 (SP4)


#2

you get that error because you specified format style 121. 121 is YYYY-MM-DD. But your date string is not really in that format. for DD-MM-YYYY, the format style should be 103

Actually, if you specify the date in string format like YYYY-MM-DD, you don't need to convert it to datetime at all

SELECT * 
FROM    dotable
WHERE
	dotableDate BETWEEN '2015-01-01' and '2015-01-25'

#3

Needs to be "YYYYMMDD" (no punctuation) for DATETIME datatype. Its seems that "YYYY-MM-DD" is unambiguous for DATE datatype, but because it has always been ambiguous for DateTime we never use that format and stick to "YYYYMMDD" (ISO format "YYYY-MM-DDThh@mm:ss.sss" is also unambiguous, requires both the punctuation and the "T" separator; no other format is "safe" without an explicit conversion as SQL's parsing rules could interpret it in all sorts of different ways depending on Server Locale, User's Language setting, and so on ...

SET LANGUAGE ENGLISH
GO
SELECT	CONVERT(datetime, '2015-12-31')
GO
SET LANGUAGE FRENCH
GO
SELECT	CONVERT(datetime, '2015-12-31')
GO

will give you an error with French language set (it is actually assuming the format is YYYY-DD-MM)

If you change DATETIME to DATE then it will work without error

Personally I hate the inconsistency and would have preferred MS has NOT "fixed" this when DATE datatype was introduced.


Can't open mdb file
#4

thank you very much