CODEWITHSUNDEEP
sqlsql-server
Paul
Paul

Reputation: 9561

Setting sql date format

I can't work out what has changed to only just start getting this error

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

I have checked that the language of the SQL Server instance is "British English" I have checked that the language of the user is "British English"

What have I missed?

select * from table where updated <= '15/09/2012'

If I run set dateformat dmy, it works, but obviously only at a session level. I need to fix it for the server

Upvotes: 0

Views: 69

Answers (2)

Mukund
Mukund

Reputation: 1689

select * from table where convert(date,updated,105) <= '15/09/2012'

can you check above query?. I hope it will work.

 declare @updated date = GetDate()

 select 
 case when (convert(date,'15/09/2012', 105) <= @updated) then 1
 else 2 end

this query is giving 1 as output, so i guess it would work for your case also.

Upvotes: 0

alroc
alroc

Reputation: 28194

A better approach which eliminates all ambiguity is to use ISO 8601 formatting for all your dates - 2012-09-15. And that will work regardless of your regional settings.

Upvotes: 2

Related Questions