Unfamiliar Date format not recognized by ISDATE()

Question

Post reply

Unfamiliar Date format not recognized by ISDATE()

John Kotuby

Ten Centuries

Points: 1079
More actions
June 20, 2011 at 8:50 pm

#252216

Hi all,
I have been tasked with creating an import program that uses data sent to us from either an AS400 or an Oracle Database.
There are some fields in the source tables that have date information in this format...
2011-06-15T23:13:02.000-04:00
SQL server 2008 and 2005 do not recognize that as a date. ISDATE() returns 0.
Implicit conversion to a DateTime field does not work. DateTime fields are being used in a current production system..so that's where the information will eventually land.
I have resorted to just grabbing the first 10 characters and am settling for the YYYY-MM-DD part just to keep the project moving.
Can anyone tell me what type of format that is? UTC of some kind maybe?
Better yet, is there a native Transact SQL function that will handle it and maybe pull the DateTime down to the minute?
Thanks for any input.

Viewing 6 posts - 1 through 5 (of 5 total)

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

drew.allen SSC Guru Points: 76776 More actions · Answer 1

This is datetimeoffset data. It was introduced in SQL2008. It combines datetime data with a time zone offset.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

John Kotuby Ten Centuries Points: 1079 More actions · Answer 2

Thanks for that information.

Where I have a problem is that I have customers that run SQL 2000 and SQL2005 and I need to include those dates in the older versions of SQL server also.

That is why I was asking if there is a native Function that will convert that DateTime type to a more conventional DateTime format readable by the older versions of SQL Server. I can now understand that there is no native Function in 2000 or 2005 because the type is new to SQL 2008.

Since there will be nightly updates of this data being fed to sites with older versions of SQL Server I guess I will need to write my own UDF to convert the input to a standard DateTime format recognized by the older versions.

Thanks for clarifying that fact for me.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

You should be able to do the conversion "inline" in your code without a UDF.

select

a.*,

NewDT = convert(datetime,left(a.DT,23)),

NewDTPlusOffset=

case left(right(a.DT,6),1)

when '-'

then convert(datetime,left(a.DT,23))-convert(datetime,right(a.DT,5))

else convert(datetime,left(a.DT,23))+convert(datetime,right(a.DT,5))

end

from

( --Test Data

select DT = '2011-06-15T23:13:02.000-04:00'union all

select DT = '2011-06-15T23:13:02.000+06:00'

) a

Results:

DT NewDT NewDTPlusOffset

----------------------------- ----------------------- -----------------------

2011-06-15T23:13:02.000-04:00 2011-06-15 23:13:02.000 2011-06-15 19:13:02.000

2011-06-15T23:13:02.000+06:00 2011-06-15 23:13:02.000 2011-06-16 05:13:02.000

drew.allen SSC Guru Points: 76776 More actions · Answer 4

One caveat is that in SQL 2008, it automatically takes DST into account, so you'll need to make sure that your UDF also takes that into account.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

John Kotuby Ten Centuries Points: 1079 More actions · Answer 5

@michael-2,

Thanks so much for the code syntax. It has saved me some time for sure. I have already incorporated it into the import and it seems to work just fine.

Regards,

John