(SQL Server) JSON Date Parsing

Demonstrates how to parse date/time strings from JSON.

Note: This example uses the DtOf and DateOf methods introduced in Chilkat v9.5.0.73

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
    DECLARE @iTmp1 int
    DECLARE @iTmp2 int
    DECLARE @iTmp3 int
    DECLARE @iTmp4 int
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    DECLARE @success int

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

    EXEC sp_OASetProperty @json, 'EmitCompact', 0

    -- First, let's create JSON containing some date/time strings.
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'test.timestamp', '2018-01-30T20:35:00Z'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'test.rfc822', 'Tue, 24 Apr 2018 08:47:03 -0500'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'test.dateStrings[0]', '2018-01-30T20:35:00Z'
    EXEC sp_OAMethod @json, 'UpdateString', @success OUT, 'test.dateStrings[1]', 'Tue, 24 Apr 2018 08:47:03 -0500'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'test.StartLoggingTime', '1446834998.695'
    EXEC sp_OAMethod @json, 'UpdateNumber', @success OUT, 'test.Expiration', '1442877512.0'
    EXEC sp_OAMethod @json, 'UpdateInt', @success OUT, 'test.StartTime', 1518867432

    EXEC sp_OAMethod @json, 'Emit', @sTmp0 OUT
    PRINT @sTmp0

    -- We've built the following JSON:

    -- {
    --   "test": {
    --     "timestamp": "2018-01-30T20:35:00Z",
    --     "rfc822": "Tue, 24 Apr 2018 08:47:03 -0500",
    --     "dateStrings": [
    --       "2018-01-30T20:35:00Z",
    --       "Tue, 24 Apr 2018 08:47:03 -0500"
    --     ],
    --     "StartLoggingTime": 1446834998.695,
    --     "Expiration": 1442877512.0,
    --     "StartTime": 1518867432
    --   }
    -- }

    -- Use the DateOf and DtOf methods to load Chilkat date/time objects with the date/time values.
    -- The CkDateTime object is primarily for loading a date/time from numerous formats, and then getting
    -- the date/time in various formats.  Thus, it's primarly for date/time format conversion.
    -- The DtObj object holds a date/time where the individual components (day, month, year, hour, minutes, etc.) are 
    -- immediately accessible as integers.
    DECLARE @dateTime int
    -- Use "Chilkat_9_5_0.CkDateTime" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.CkDateTime', @dateTime OUT

    DECLARE @dt int
    -- Use "Chilkat_9_5_0.DtObj" for versions of Chilkat < 10.0.0
    EXEC @hr = sp_OACreate 'Chilkat.DtObj', @dt OUT

    DECLARE @getAsLocal int
    SELECT @getAsLocal = 0

    -- Load the date/time at test.timestamp into the dateTime object.
    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.timestamp', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0
    EXEC sp_OAMethod @dateTime, 'GetAsUnixTime', @iTmp0 OUT, 0
    PRINT @iTmp0
    EXEC sp_OAMethod @dateTime, 'GetAsRfc822', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.rfc822', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    EXEC sp_OASetProperty @json, 'I', 0
    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.dateStrings[i]', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    EXEC sp_OASetProperty @json, 'I', 1
    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.dateStrings[i]', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.StartLoggingTime', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.Expiration', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'test.StartTime', @dateTime
    EXEC sp_OAMethod @dateTime, 'GetAsTimestamp', @sTmp0 OUT, @getAsLocal
    PRINT @sTmp0

    -- Output so far:

    -- 	2018-01-30T20:35:00Z
    -- 	1517344500
    -- 	Tue, 30 Jan 2018 20:35:00 GMT
    -- 	2018-04-24T13:47:03Z
    -- 	2018-01-30T20:35:00Z
    -- 	2018-04-24T13:47:03Z
    -- 	2015-11-07T00:36:38Z
    -- 	2015-09-22T04:18:32Z
    -- 	2018-02-17T17:37:12Z

    -- Now load the date/time strings into the dt object:
    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.timestamp', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.rfc822', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    EXEC sp_OASetProperty @json, 'I', 0
    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.dateStrings[i]', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    EXEC sp_OASetProperty @json, 'I', 1
    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.dateStrings[i]', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.StartLoggingTime', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.Expiration', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    EXEC sp_OAMethod @json, 'DtOf', @success OUT, 'test.StartTime', @getAsLocal, @dt

    EXEC sp_OAGetProperty @dt, 'Month', @iTmp0 OUT

    EXEC sp_OAGetProperty @dt, 'Day', @iTmp1 OUT

    EXEC sp_OAGetProperty @dt, 'Year', @iTmp2 OUT

    EXEC sp_OAGetProperty @dt, 'Hour', @iTmp3 OUT

    EXEC sp_OAGetProperty @dt, 'Minute', @iTmp4 OUT
    PRINT 'month=' + @iTmp0 + ', day=' + @iTmp1 + ', year=' + @iTmp2 + ', hour=' + @iTmp3 + ', minute=' + @iTmp4

    -- Output:

    -- month=1, day=30, year=2018, hour=20, minute=35
    -- month=4, day=24, year=2018, hour=13, minute=47
    -- month=1, day=30, year=2018, hour=20, minute=35
    -- month=4, day=24, year=2018, hour=13, minute=47
    -- month=11, day=6, year=2015, hour=18, minute=36
    -- month=9, day=21, year=2015, hour=23, minute=18
    -- month=2, day=17, year=2018, hour=11, minute=37

    EXEC @hr = sp_OADestroy @json
    EXEC @hr = sp_OADestroy @dateTime
    EXEC @hr = sp_OADestroy @dt


END
GO