Setting sql date format

Question

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

Do you have the option to explicitly cast your date literal?, i.e. change the SQL syntax — Nick.Mc
– Nick.Mc, Commented Sep 22, 2014 at 12:47
But why use that format and (a) not the ISO Format that alawys works or (b) use a parameter — TomTom
– TomTom, Commented Sep 22, 2014 at 12:47

alroc · Accepted Answer · 2014-09-22 12:47:38Z

2

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.

answered Sep 22, 2014 at 12:47

alroc

28.3k6 gold badges55 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Paul Over a year ago

While probably right, it's not particularly helpful - this would involve rewriting a fair bit of the code, where it just needs to work for the moment...

alroc Over a year ago

You can do it right, or you can do it twice. Which one is more expensive?

Paul Over a year ago

I'm not sure why my question itself is being brought in to question...currently, doing it twice is more expensive. There isn't the budget available for someone to go through the codebase and replace all date instances. It's an inherited codebase, and it's not very well built

Dan Guzman Over a year ago

An even better option is to use parameterized queries instead of passing date literals. That avoids the date format ugliness, is more secure, and provides performance benefits.

alroc Over a year ago

Because your imagined solution is merely a band-aid, and a weak one at that. You have no assurance that your proposed solution won't cause problems with other queries. OTOH, the ISO format suggestion (which you've gotten 4 times now) will work in all cases. Fix the root cause - ambiguous date interpretation.

|

Mukund · Accepted Answer · 2014-09-22 12:57:11Z

0

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.

answered Sep 22, 2014 at 12:57

Mukund

1,6871 gold badge12 silver badges20 bronze badges

Collectives™ on Stack Overflow

Setting sql date format

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related