CODEWITHSUNDEEP
sqldatetime
user2871190
user2871190

Reputation: 251

SQL Datetime Convert Error

I have have an SQL Statement which returns following error:

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

SELECT * FROM eBV_Platz 
WHERE (ADRID = 4436) AND (ID <> 5) AND (Status = 1)  
AND (CONVERT(DATETIME, '01.03.2014', 102) >= PlaceFrom) 
AND (CONVERT(DATETIME, '01.03.2014')  <= PlaceTo)
OR (CONVERT(DATETIME, '31.03.2014') >= PlaceFrom) 
AND (CONVERT(DATETIME, '31.03.2014') <= PlaceTo)

But this one works fine and the only difference are the date values:

SELECT * FROM eBV_Platz 
WHERE (ADRID = 4436) AND (ID <> 5) AND (Status = 1)  
and (CONVERT(DATETIME, '01.01.2000', 102) >= PlaceFrom) 
AND (CONVERT(DATETIME, '01.01.2000')  <= PlaceTo)
OR (CONVERT(DATETIME, '01.06.2001') >= PlaceFrom) 
AND (CONVERT(DATETIME, '01.06.2001') <= PlaceTo)

I really don't understand this. Can anybody help me?

Upvotes: 0

Views: 473

Answers (2)

crthompson
crthompson

Reputation: 15865

I'm betting that the second one does not work fine, rather it converts your dates to january 06 and january 01.

You need to give it a hint that you are using a day month year format.

Try instead:

(CONVERT(DATETIME, '31.03.2014', 103)

The 103 (from MSDN) interprets the date as dd/mm/yy

As @AlexK noted in the comments, these dont really need to be converted. You could simply use the strings as long as they were in a better format.

My assumption here is that you are using MSSQL. For a different platform, the syntax would be different.

Upvotes: 1

Kevin Hogg
Kevin Hogg

Reputation: 1781

As stated in the comments, use ISO 8601 date format to specify dates (yyyy-mm-ddThh:mm:ss[.mmm]).

This would change '31.03.2014' to '2014-03-31T00:00:00.000' and remove any ambiguity.

Upvotes: 0

Related Questions