CODEWITHSUNDEEP
sqlsql-servert-sql
Tashi
Tashi

Reputation: 11

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

My SQL Server WHERE condition is below.

WHERE ([Orders].ORTakenDate >= CONVERT(DATETIME, '2014-08-13 00:00:00', 105) 
AND [Orders].ORTakenDate <= CONVERT(DATETIME, '2014-09-29 00:00:00', 105))

the datatype is datetime2, when I execute this gives me error:

ERROR

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

Upvotes: 0

Views: 3888

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Why are you using format 105 for this? Use the right format for YYYY-MM-DD format, 121:

        WHERE ([Orders].ORTakenDate >= CONVERT(DATETIME, '2014-08-13 00:00:00', 121) AND
               [Orders].ORTakenDate <= CONVERT(DATETIME, '2014-09-29 00:00:00', 121))

The formats are documented here.

EDIT:

SQL Server generally recognizes date constants in the format YYYY-MM-DD, so you should be able to simplify this to:

        WHERE ([Orders].ORTakenDate >= '2014-08-13' AND
               [Orders].ORTakenDate <= '2014-09-29'
              )

(There is one case with an international format where the date constant is not interpreted correctly.)

Upvotes: 1

I A Khan
I A Khan

Reputation: 8841

when you use 105 then date should be in dd-MM-yyyy hh:mm:ss formate..

so use like this 

WHERE ([Orders].ORTakenDate >= CONVERT(DATETIME, '13-08-2014 00:00:00', 105) 
AND [Orders].ORTakenDate <= CONVERT(DATETIME, '29-09-2014 00:00:00', 105))

Update :

If you are passing date from C# then first convert value in dd-MM-yyyy hh:mm:ss formate..

Upvotes: 0

Navneet
Navneet

Reputation: 447

in SQL it might be 

WHERE ([Orders].ORTakenDate >= cast('2014-08-13 00:00:00' as datetime) 
                  AND [Orders].ORTakenDate <= cast('2014-09-29 00:00:00' as datetime)

------------------------OR--------------------------------------------------

WHERE ([Orders].ORTakenDate >= CONVERT(DATETIME, '13-08-2014 00:00:00', 105) 
                  AND [Orders].ORTakenDate <= CONVERT(DATETIME, '29-09-2014 00:00:00', 105))

Upvotes: 0

apomene
apomene

Reputation: 14389

in 105 format date '2014-09-29 00:00:00' considers 29 as month which is out of range.

So either you should re-arrange your strings or use different format

Upvotes: 0

Related Questions