Date conversion - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Date conversion

Author

Topic

SMerrill
Posting Yak Master

206 Posts

Posted - 2010-07-02 : 00:24:50

I wonder if anyone wants to help translate from yyymmddhhmmss.mmmmmmsutc XML format into a SQL datetime.

NOTE: In the date-time format, yyyy is a 4 digit year, mm is the month, dd is the day, hh is the hour on a 24-hour clock, mm is the minute, ss is the second, mmmmmm is the number of microseconds, s is the sign of the Universal Coordinated Time (UTC) sign correction field (plus [+], minus [–], or colon [:] (in which case, the value would be interpreted as a time interval and yyyymm interpreted as days), and utc is the offset from UTC in minutes (using the sign indicated by s). For example, Tuesday, March 15, 2005, at 4:30:15 PM EST would be represented as: 20050315163015.0000000-300.

~ Shaun Merrill
Seattle area

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-02 : 05:06:26

[code]DECLARE @Sample VARCHAR(40) = '20050315163015.0000000-300'

SET @Sample = STUFF(STUFF(STUFF(@Sample, 5, 0, '-'), 8, 0, '-'), 11, 0, ' ') -- Date part
SET @Sample = STUFF(STUFF(@Sample, 14, 0, ':'), 17, 0, ':') -- Time part
SET @Sample = STUFF(@Sample, LEN(@Sample) - 1, 0, ':') -- Zone part

SELECT @Sample AS Original,
CAST(@Sample AS DATETIMEOFFSET(7)) AS Converted
GO
DECLARE @Sample VARCHAR(40) = '20050315163015.0000000-300'

SET @Sample = STUFF(@Sample, 9, 0, ' ') -- Date part
SET @Sample = STUFF(STUFF(@Sample, 12, 0, ':'), 15, 0, ':') -- Time part
SET @Sample = STUFF(@Sample, LEN(@Sample) - 1, 0, ':') -- Zone part

SELECT @Sample AS Original,
CAST(@Sample AS DATETIMEOFFSET(7)) AS Converted
GO[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources