Expression to convert datetime to UTC. Is there a better way?

• sam.dahl

  Hall of Fame

  Points: 3112

  More actions

  April 13, 2014 at 7:58 pm

  #290246

  Hi,

  I have a SSIS 2008 package that is loading audit and logging information into a 2008 R2 sql server database.

  My aim is to store unambiguous datetimes and my reading has lead me to datetimeoffset being the most appropriate solution.

  I'm recording the package start time @[System::StartTime] via a stored procedure using an Execute SQL Task.

  DT_DBTIMESTAMP doesn't work as a parameter type and DT_DATE doesn't guarantee anything more than hour-level accuracy according to Jamie here: http://consultingblogs.emc.com/jamiethomson/archive/2005/11/15/SSIS_3A00_-What_2700_s-the-difference-between-DT_5F00_DATE-and-DT_5F00_DBTIMESTAMP.aspx (although I've used DT_DATE before and not noticed an issue with truncation)

  I believe the next best thing (and recommended on MSDN) is to use a varchar which means constructing the string myself.

  After reading through Todd's post here: http://toddmcdermid.blogspot.com.au/2010/04/working-with-utc-dates-in-integration.html

  and various other resources I've come up with the following expression to convert the package start time into a UTC datetime with local time offset:

  (DT_WSTR,33)((DT_WSTR,4)DATEPART("yyyy", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])) + "-" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("mm", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])), 2) + "-" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("dd", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])), 2) + "T"+

  RIGHT("0" + (DT_WSTR,2)DATEPART("Hh", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])), 2) + ":" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("mi", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])), 2) + ":" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("ss", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])), 2) + "." +

  RIGHT("000000" + (DT_WSTR,7)DATEPART("Ms", DATEADD("minute",-DATEDIFF("minute", GETUTCDATE(), GETDATE()),@[System::StartTime])), 7) +

  (DT_WSTR,1)(SIGN(DATEDIFF("minute", GETUTCDATE(), GETDATE()))<0?"-":"+") +

  RIGHT("0" + (DT_WSTR,2)(DT_I2)(ABS(DATEDIFF("minute", GETUTCDATE(), GETDATE()))/ 60), 2) + ":" +

  RIGHT("0" + (DT_WSTR,2)(ABS(DATEDIFF("minute", GETUTCDATE(), GETDATE())) % 60),2))

  (Note: I've updated this expression at the end of my post)

  I'm using this to set the value of a new variable and it should give me a consistent, unambiguous UTC datetime with local time offset. (I know the package start time variable doesn't give the equivalent precision of datetime2 but I've written the expression to allow for it if I reuse it for something more precise).

  However, this all seems like a lot of effort to do something which should in theory be fairly straightforward. Is there a better/simpler way?

  The only other approach that comes to mind is to write a script and use the .NET functions to do the conversion. But that would mean an extra task to set the variable before the SQL task to call the sproc, or else handling a db connection in the script.

  Thoughts?

  P.S.

  Is it better to store the UTC datetime with local time offset or local datetime with UTC offset?

  I've seen suggestions both ways but no reasons given.

  P.P.S.

  Based on the following:

  1. http://msdn.microsoft.com/en-us/library/ms180878(v=sql.105).aspx

  Conversion from datetimeoffset

  When you convert from datetimeoffset to the following non–time zone types, style 0 (default) always indicates that the return date, time, datetime2, datetime, or smalltime value is in local format of the preserved time zone offset; and style 1 always indicates UTC format.

  2.

  DECLARE

  @dtoffset DATETIMEOFFSET;

  SELECT @dtoffset = CONVERT(DATETIMEOFFSET,'2014-04-11T01:44:13.0000000+10:00')

  SELECT CONVERT(DATETIME2,@dtoffset,1)

  Results: 2014-04-10 15:44:13.0000000

  This means that my datetimeoffset should store local datetime with offset of timezone (GMT+10 for Melbourne/Sydney)?

  CONVERT(DATETIME2,@dtoffset,0) then gives local datetime

  CONVERT(DATETIME2,@dtoffset,1) then gives UTC datetime

  Therefore my expression should have been:

  (DT_WSTR,33)((DT_WSTR,4)DATEPART("yyyy", @[System::StartTime] ) + "-" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("mm", @[System::StartTime] ), 2) + "-" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("dd", @[System::StartTime] ), 2) + "T"+

  RIGHT("0" + (DT_WSTR,2)DATEPART("Hh", @[System::StartTime] ), 2) + ":" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("mi", @[System::StartTime] ), 2) + ":" +

  RIGHT("0" + (DT_WSTR,2)DATEPART("ss", @[System::StartTime] ), 2) + "." +

  RIGHT("000000" + (DT_WSTR,7)DATEPART("Ms", @[System::StartTime] ), 7) +

  (DT_WSTR,1)(SIGN(DATEDIFF("minute", GETUTCDATE(), GETDATE()))<0?"-":"+") +

  RIGHT("0" + (DT_WSTR,2)(DT_I2)(ABS(DATEDIFF("minute", GETUTCDATE(), GETDATE()))/ 60), 2) + ":" +

  RIGHT("0" + (DT_WSTR,2)(ABS(DATEDIFF("minute", GETUTCDATE(), GETDATE())) % 60),2))

  And this answers some of my questions above.

  P.P.P.S

  I guess the reasons I'm still posting this are because I found it confusing to get my head around at first and hopefully someone can validate that I'm on the right path.

  And hopefully it might help someone else.

  Thanks,

  Sam