CODEWITHSUNDEEP
sqlsql-server-2008datetime-format
Nik
Nik

Reputation: 7273

SQL Server 2008 DATETIME2 Format Question

I am dealing with a date/time from Australia (I am located in the USA). I am unable to get the following string to insert into a DATETIME2 column:

2010/19/10 04:38:12.892

As you can see, it's formatted in yyyy/dd/mm HH:MM:ss.MMM format. I understand that the normal format is yyyy-mm-dd HH:MM:ss.MMM. What I am wondering is if there is a locality setting on SQL Server that I can change to get it to accept this format, or if I need to parse it and rearrange it myself.

EDIT: Just for your information, I have been importing a mm/dd/YYYY HH:MM:ss.MMM format string into the field just fine.

Upvotes: 1

Views: 5407

Answers (3)

Icarus
Icarus

Reputation: 63956

You can try this:

SET DATEFORMAT YDM;
select cast('2010/19/10 04:38:12.892' as datetime)

And it will parse it correctly

UPDATE: I found help here.

But I tried casting to datetime2 directly and it didn't work. I don't understand why you can cast to datetime and not datetime2. Perhaps a good question for SO. :)

Upvotes: 0

a1ex07
a1ex07

Reputation: 37354

Try issuing SET DATEFORMAT ydm; before INSERT. Then CONVERT(DATETIME,('2010/19/10 04:38:12.892')); works fine.

More info

Upvotes: 0

Louis
Louis

Reputation: 2890

It depends on the "locale" date format, I guess.

Some samples on how to convert here : http://www.java2s.com/Code/SQLServer/Date-Timezone/Formatdatemmddyyyy.htm

Hope this was helpful.

Upvotes: 2

Related Questions