CODEWITHSUNDEEP
sql-server-2005t-sql
Thomas
Thomas

Reputation: 34188

datetime conversion issue in sql server

if i try to cast string date to datetime like select cast('12/01/2010' as datetime) then it works

but if i try to cast like select cast('22/01/2010' as datetime) then it is giving error.

again if i try to cast string date to datetime like select cast('2010/12/01' as datetime) then it works but if i try to cast like select cast('2010/25/01' as datetime) then it is giving error. my requirement is whatever way user input date that should be successfully converted to datetime. please tell me best solution

Upvotes: 0

Views: 163

Answers (2)

Baatar
Baatar

Reputation: 221

I recommend you to use fixed '20111231' format. You do not need to use cast, convert or similar command.

Upvotes: 1

Oleg Dok
Oleg Dok

Reputation: 21756

In your case the sql server assumes, that date format id mm/dd/yyyy - US format,

you want to use French format, so use convert

select CONVERT(DATETIME, '12/01/2010', 103)

instead you'll get an error because there in no such a month number - 22

The best solution when passing datetime as string - to use short(without timezone) ISO format:

yyyyMMdd HH:mm:ss.ffff

Upvotes: 1

Related Questions