SQL to convert UTC date to datetime

Question

Post reply

SQL to convert UTC date to datetime

scott d cowan

Old Hand

Points: 318
More actions
August 27, 2004 at 4:57 am

#360847

Can someone tell me how to convert a utc time into a datetime for display purposes via SQL.
Thanks
Scott

Viewing 8 posts - 1 through 8 (of 8 total)

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

UTC date times are usually just date times based on UTC location. If it is something else then what are you seeing?

scott d cowan Old Hand Points: 318 More actions · Answer 2

The UTC date is held as decimal(20,0) ( ?? number of seconds since 19xx I think ) - I need to convert this ( via SQL ) to a display date - e.g. dd/mm/yyyyy hh:mm:ss.

Thanks

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

You should post some input data along with what it should represent. That might be more descriptive.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Sounds like you have an application running over these tables and you are reporting. Usually the date is the number of seconds since Jan 1 1970. All you need to do is the do a dateadd to get the right date.

SELECT DATEADD(s,'19700101',colVal) FROM ...

I also posted code for a procedure to Convrt GMT to local if you need. It can easily be converted to a user defined function.

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 5

IF you are using standard field storage for UTC then the below should work. Please note that this code will return Greenwich Mean time (Zulu time for you pilots...)

-- 1994-11-05T08:15:30-05:00

-- 1994-11-05T13:15:30Z

DECLARE @UTC VARCHAR(25)

DECLARE @Date DATETIME

DECLARE @HH SMALLINT

DECLARE @mm SMALLINT

SET @UTC = '1994-11-05T08:15:30-05:00'

-- SET @UTC = '1994-11-05T13:15:30Z'

SET @Date = LEFT(@UTC, 10) + SPACE(1) + SUBSTRING(@UTC, 12, 8)

IF RIGHT(RTRIM(LTRIM(@UTC)), 1) = 'Z'

BEGIN

SELECT @Date

END

ELSE

BEGIN

SET @HH = -1 * SUBSTRING(@UTC, 20, 3)

SET @mm = -1 * SUBSTRING(@UTC, 20, 1) + SUBSTRING(@UTC, 24, 2)

SET @Date = DATEADD(HOUR, @HH, @Date)

SET @Date = DATEADD(MINUTE, @mm, @Date)

SELECT @Date

END

Good Hunting!

AJ Ahrens

webmaster@kritter.net

scott d cowan Old Hand Points: 318 More actions · Answer 6

Many thanks - it sounds like that will add 19,700,101 seconds to the date but i will give it a try.

scott d cowan Old Hand Points: 318 More actions · Answer 7

scott d cowan

Old Hand

Points: 318

September 1, 2004 at 10:28 am

#521387

Many thanks , will give it a try.