x

Dates and Collation Issue?

We have a PC that runs a script every hour to pull data from an Oracle table and inserts it into a SQL table, so that we can report on some key metrics. All was fine until the start of August when it started inserted dates in US not UK format! i.e. Data for August 10th was inserted as "2011-10-08 17:02:21.000" when it should have been "2011-08-10 17:02:21.000". The PC definitely has UK set as its region and the collation of the table is Latin1_ General_ CI_AS, which I believe is correct for UK. Can anyone think of where I shoudl be looking as I've run out of ideas? Thanks.
more ▼

asked Aug 11, 2011 at 05:54 AM in Default

JohnStaffordDBA gravatar image

JohnStaffordDBA
195 8 8 9

I think the problem was that the system started AFTER the 12th of July so that dates were converting OK, but once we got to 1st of August they were interpreted wrongly.

It's a 3rd party scripting product so is proving "fun" to fix - luckily my boss has moved it to the low priority pile.

Will post fix when I have one!
Sep 23, 2011 at 09:00 AM JohnStaffordDBA
Have you looked at a trace to see what TSQL the app is generating?
Sep 27, 2011 at 01:06 AM ThomasRushton ♦
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

Have a look at [SET DATEFORMAT][1]

Or use a string format which always works for dates in SQL Server - eg YYYYMMDD hh:mm:ss.nnn

[1]: http://msdn.microsoft.com/en-us/library/ms189491.aspx
more ▼

answered Aug 11, 2011 at 06:12 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
you might investigate using a trigger to check the inserted value but that may cause more issues. Can you import it to a staging table somewhere and do a sanity check on the data? Or replace the script with a SSIS package to handle the date format for you?
more ▼

answered Sep 23, 2011 at 09:10 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.8k 75 79 108

(comments are locked)
10|1200 characters needed characters left

Whatever your date settings in SQL Server, you can get any known format of date if it is consistent. If you are processing a data feed it is best to make it explicit, rather than to rely on the date format settings of your server (Oh boy, the errors that are caused when the server moves over the Atlantic) The simplest way to do this is if the day and month are reversed to what you expect (We brits like to go Day Month Year) This means that ....

    SELECT CONVERT(DATETIME,'25/3/2011',103)

. .. and ...

    SELECT CONVERT(DATETIME,'3/25/2011',101)

will give you the same DateTime In your case, you have a bug, frankly since there is no known date format (international standard time format) in SQL Server that goes yyyy-dd-mm. Instead, you have to resort to pesky string manipulation.

    SELECT CONVERT(DATETIME,SUBSTRING(Thedate,6,5)
        +'-'+LEFT(TheDate,4)+ RIGHT(TheDate,13),103) 
      FROM (SELECT  '2011-10-08 17:02:21.000' AS theDate )f
but be there when they fix the bug!!
more ▼

answered Sep 26, 2011 at 11:40 AM

Phil Factor gravatar image

Phil Factor
3.9k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x27
x22

asked: Aug 11, 2011 at 05:54 AM

Seen: 2208 times

Last Updated: Aug 11, 2011 at 05:54 AM