Datetime shenanigans

Question

Post reply

Datetime shenanigans

Jamie Thomson

SSChampion

Points: 11805
More actions
November 21, 2005 at 6:26 am

#89964

I have the following query:
UPDATE
table
SET
datetime_field = '2005-11-21'
that fails with:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
whereas the following works fine:
set
dateformat ymd
UPDATE
ModuleDetail
SET
ToDate = '2005-11-21'
WHERE
ModuleDetailID = 60
Now, I found an excellent article from Frank (whom will no doubt reply to this ) where he advised to basicllay always use convert with the 112 parameter to sort out day/month ambiguities and that has solved my problem - thanks Frank!

I want to understand why it was happening in the first place though. Here are various settings:
Database collation=SQL_Latin1_General_CP1_CI_AS
SELECT @@language returns 'British'
sp_configure
'default language' returns config value of 23

Everything seems to suggest I am British and and so I don't understand why the SQL statement at the top of here doesn't work.

Can anyone help to explain this or tell me how/where to investigate?
Thanks
Jamie
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply

Roy Rogers SSCertifiable Points: 7712 More actions · Answer 1

I would assume for British

DD/MM/YYYY and YYYY-DD-MM formats will work. Date comes before month.

Regards,
gova

Ian Yates SSCoach Points: 19738 More actions · Answer 2

Are you executing from Query Analyser, some database API, via OSQL, etc? Perhaps your connection library is overriding some of your parameters. I've always found the yyyy-mm-dd format to work in all cases (I'm in Australia and usually have dates in dd/mm/yyyy but occasionally work on servers where lazy admins haven't changed from the mm/dd/yyyy backwards format that Americans believe the entire world uses!)

In any case, I would see what sort of client side API you are using and if it impacts on your code - try it in QA and see if you get the same bad results.

Cheers

Jamie Thomson SSChampion Points: 11805 More actions · Answer 3

Thankyou for the comments guys.

Ian, the problem first occurred in SSIS (for this is SQL Server 2005) and I then pasted the same SQL into SSMS and managed to repro the problem.

Govinn, I see your thinking here but it just doesn't make any sense. Any brit will tell you that YYYY-DD-MM would never be used. Strange!

Any more???

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Always use explicit conversion:

UPDATE table

SET

datetime_field = convert(datetime, '2005-11-21', 120)

_____________
Code for TallyGenerator

Jamie Thomson SSChampion Points: 11805 More actions · Answer 5

Sergiy,

Thanks for that. However, as I said above I already have a workaround that works fine (and in fact the workaround I actually used was exactly as you have given - an explicit cast with parameter 112).

What I want to know is WHY it happens in the first place? WHY does '2005-11-21' not get evaluated as 21st November?

Thanks

Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

noeld SSC Guru Points: 96590 More actions · Answer 6

What I want to know is WHY it happens in the first place? WHY does '2005-11-21' not get evaluated as 21st November?

Because the language which SQL server was installed ( or at least the connection was made from ) is BRITISH therefore it will try to interpret yyyy-dd-mm

you can make a couple of tests to double check :

when connecting run: set language us_english and your First statement will work run set language British and it will fail again

secondly you can override that with what you alredy did : set dateformat ymd

Cheers,

* Noel

Jamie Thomson SSChampion Points: 11805 More actions · Answer 7

Thanks Noel, I knew you'd have something to say when you came across this

If this is true then my mistake here is that I assumed anything on a British connection would get evaluated as YYYY-MM-DD. British people always put the month in the middle, regardless of what comes first or last!

As I said, it isn't causing a problem. I just wanted to know why this was happening.

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

noeld SSC Guru Points: 96590 More actions · Answer 8

noeld

SSC Guru

Points: 96590

November 21, 2005 at 2:46 pm

#605077

Now you know!

* Noel

Jamie Thomson SSChampion Points: 11805 More actions · Answer 9

Sorry Noel. I've just been checking this out and I don't think we've quite got to the bottom of it. Execute the following (I did it after logging on as sa):

use

tempdb

go

create

table test

(

col1

datetime

)

GO

sp_defaultlanguage

'sa', 'us_english'

GO

insert

into test values ('2005-10-11')

GO

sp_defaultlanguage

'sa', 'British'

GO

insert

into test values ('2005-10-11')

GO

select

* from test

GO

If what you were saying is true then you would expect there to be 2 different values in test would you not? Well that's the case. They're both the same.

Could you try this out?

Thanks

Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

Sergiy SSC Guru Points: 110208 More actions · Answer 10

It's not about language.

It's about SET DATEFORMAT.

_____________
Code for TallyGenerator

Jamie Thomson SSChampion Points: 11805 More actions · Answer 11

Hi Sergiy,

OK, and what determines the DATEFORMAT?

I have executed

select

* from sys.syslanguages

and can see a 'dateformat' field in there. So it seems to me that the dateformat is determined by the language hence it IS all about language. Am I wrong about that?

Assuming this is correct...the dateformat for 'British' is 'dmy' which still doesn't help to explain why

UPDATE table SET datetime_field = '2005-11-21'

gives me an error!!

-Jamie

Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson

noeld SSC Guru Points: 96590 More actions · Answer 12

Jaime, IT IS about the language!!!

if you are trying to use sp_setdefault language you probably need to disconnect and connect after each call to that procedure

If you look at my code, I am using

SET LANGUAGE xxx -- not a call to the procedure you used

create table test

(

col1 datetime

)

set language 'British'

insert into test(col1) values ('2005-10-11')

set language 'us_english'

insert into test(col1) values ('2005-10-11')

select * from test

col1

2005-11-10 00:00:00.000

2005-10-11 00:00:00.000

and by the way I do get two different dates because SET Language changes the connection language on the fly!!

Cheers,

* Noel

Sergiy SSC Guru Points: 110208 More actions · Answer 13

Because in 'dmy' day cames before month!

Sounds silly, but that is it.

And 'British' settings do not guarantee 'dmy' dateformat. 'dmy' is DEFAULT for 'British', but you can set any option without changing language.

Can you be sure nobody from developers is changing DATEFORMAT according to their "this minute" needs inside any of SPs they created?

So, don't rely on settings out of your control. Always do datetime conversions explicitly. And try to avoid it.

_____________
Code for TallyGenerator

noeld SSC Guru Points: 96590 More actions · Answer 14

Personally I use ISO format for character dates and you won't need the explicit conversion at all

TEST:

create table test

(

col1 datetime

)

set language 'British'

insert into test(col1) values ('20051011')

set language 'us_english'

insert into test(col1) values ('20051011')

select * from test

col1

2005-10-11 00:00:00.000

Cheers!

* Noel