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.
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'
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.