I got some smashing advice from a guy called Steve Kass today that I wanted to make sure I kept and remembered. I thought about the best place to store it and I thought, well why not right here on my blog. That way I won't forget it and maybe other people can benefit too.
Thanks for your help Steve.
====================================================================
I was having a problem with some data I was inserting into SQL Server from SSIS. I was finding that SQL Server was ambiguously interpreting the datetime strings that I was passing to it from my SSIS Execute SQL Task. (I talked about this problem previously here.)
I was passing my data in the form "YYYY-MM-DD HH:MI:SS". Depending on the language setting of the user with which I was establishing the connection, SQL Server would sometimes interprete this as "YYYY-DD-MM HH:MI:SS" which on any day after the 12 day of any month would throw an error.
Then along comes Steve and sorts out the problem for me. It turns out that the ONLY unambiguous formats for a datatime passed as a string are:
- yyyymmdd hh:mi:ss &
- yyyy-mm-ddThh:mi:ss
I didn't know that but I'm sure glad that I do now.
Steve also provided some sample coded to illustrate the point. paste the following into a Query Analyser (in SQL2000) or SSMS (in SQL2005) window and run it:
set language French
go
select datename(month,s) as mnth,
cast(s as datetime) as dt
from (
select '2005-04-03 18:00:00' as s
union all select '2005-04-03T18:00:00'
union all select '20050403 18:00:00'
) T
go
set language English
go
select datename(month,s) as mnth,
cast(s as datetime) as dt
from (
select '2005-04-03 18:00:00' as s
union all select '2005-04-03T18:00:00'
union all select '20050403 18:00:00'
) T
go
You'll get the following output
As you can see, using a format of YYYY-MM-DD HH:MI:SS can result in different results but using the two formats described above there is no such ambiguity.
========================================================
If you're reading this Steve....I'm very grateful!
-Jamie