0

How can I change the Datetime values for the existing data to UTC Time? Is any inbuilt SQL qry available to change?

Eg. Existing Data in the table

CreatedON
2013-03-01 08:12:00.000
2013-03-01 13:15:18.000
2013-03-01 13:10:00.000

Need to convert this data to UTC time which is recorded as GMT Time during creation. Any suggestions will be helpful

vhadalgi
  • 7,027
  • 6
  • 38
  • 67
SESHI
  • 33
  • 2
  • 7
  • How can I identify the UTC time for existing data? eg: 2013-03-01 08:12:00.000 wat will be the UTC Time for the given date? – SESHI Dec 17 '13 at 09:08

1 Answers1

1

try this:

declare @a datetime 
set @a='2013-03-01 08:12:00.000'

select  DateAdd(s,DateDiff(s, getdate(),getutcdate()),@a )
vhadalgi
  • 7,027
  • 6
  • 38
  • 67
  • Will the qry be applicable for other dates like '2013-04-05 08:10:00', greater than the above dates? – SESHI Dec 17 '13 at 09:26
  • @SESHI :: it'll work if you give @a=''2013-04-05 08:10:00.000' utc will be '2013-04-05 02:40:00.000' – vhadalgi Dec 17 '13 at 09:31
  • Im getting the below result when I execute '2013-04-05 07:10:00.000' SYSDATETIMEOFFSET() - 2013-12-17 11:04:59.9216720 +01:00 getutcdate() - 2013-12-17 10:04:59.920 Getdate() - 2013-12-17 11:04:59.920 – SESHI Dec 17 '13 at 10:04
  • i'm not getting what u'r telling? – vhadalgi Dec 17 '13 at 10:11
  • you made any changes to this answer or is it jus fine? – vhadalgi Dec 17 '13 at 12:18