Time Zone for database timestamps - EnterDateTime LastChgDateTime RRS feed

  • General discussion

  • Has anyone noticed that the database timestamp fields (EnterDateTime LastChgDateTime) as presented in the MDS Integration Views appear to be stored using the GMT / UTC timezone, not the MDS server's local timezone?  Looking into the view DDL it seems those columns are coming straight from the underlying MDS tables, so I guess that's how they are being stored.

    The MDS web UI shows them converted to the local timezone.

    I'm building a report on top of the MDS Integration View, so I guess I'll handle the conversion with a SQL expression e.g.

    SELECT *
    , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [My MDS Entity].EnterDateTime) 
           AS EnterDateTime_InLocalTime
    , DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), [My MDS Entity].LastChgDateTime) 
           AS LastChgDateTime_InLocalTime
    FROM [Master_Data_Services].[mdm].[My MDS Entity]

    Am I on the right track?

    Thursday, November 1, 2012 11:51 PM