(SQL Server) How to Parse a TimeStamp (such as 2016-11-11T14:32:17.0908971Z)

Timestamps are frequently used in REST API responses. This example demonstrates how to parse a timestamp string to get at the date/time components in the local timezone or in the GMT/UTC timezone.

Chilkat ActiveX Downloads ActiveX for 32-bit and 64-bit Windows

-- Important: See this note about string length limitations for strings returned by sp_OAMethod calls.
--
CREATE PROCEDURE ChilkatSample
AS
BEGIN
    DECLARE @hr int
    DECLARE @iTmp0 int
    -- Let's say we have a timestamp string such as 2016-11-11T14:32:17.0908971Z
    DECLARE @strTimestamp nvarchar(4000)
    SELECT @strTimestamp = '2016-11-11T14:32:17.0908971Z'

    DECLARE @dateTime int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dateTime OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

    DECLARE @success int
    EXEC sp_OAMethod @dateTime, 'SetFromTimestamp', @success OUT, @strTimestamp

    -- Get a DtObj in the local timezone.
    DECLARE @bLocalTimezone int
    SELECT @bLocalTimezone = 1
    DECLARE @dt int
    EXEC sp_OAMethod @dateTime, 'GetDtObj', @dt OUT, @bLocalTimezone

    -- Get the individual date/time components

    PRINT '-- Local Time --'

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp0 OUT
    PRINT 'Year: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT
    PRINT 'Month: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp0 OUT
    PRINT 'Day: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp0 OUT
    PRINT 'Hour: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp0 OUT
    PRINT 'Minutes: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Second', @iTmp0 OUT
    PRINT 'Seconds: ' + @iTmp0

    EXEC @hr = sp_OADestroy @dt

    -- Get a DtObj in the GMT/UTC timezone.
    SELECT @bLocalTimezone = 0
    EXEC sp_OAMethod @dateTime, 'GetDtObj', @dt OUT, @bLocalTimezone

    -- Get the individual date/time components

    PRINT '-- UTC Time --'

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp0 OUT
    PRINT 'Year: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT
    PRINT 'Month: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp0 OUT
    PRINT 'Day: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp0 OUT
    PRINT 'Hour: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp0 OUT
    PRINT 'Minutes: ' + @iTmp0

    EXEC sp_OAGetProperty @dt, 'Second', @iTmp0 OUT
    PRINT 'Seconds: ' + @iTmp0

    EXEC @hr = sp_OADestroy @dt


    EXEC @hr = sp_OADestroy @dateTime


END
GO