(SQL Server) Parse a Microsoft JSON Date (MS AJAX Date)

Demonstrates how to parse a Microsoft JSON Date, also known as an MSAJAX date.

Note: This example requires Chilkat v9.5.0.76 or greater.

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
    -- Important: Do not use nvarchar(max).  See the warning about using nvarchar(max).
    DECLARE @sTmp0 nvarchar(4000)
    -- Note: This example requires Chilkat v9.5.0.76 or greater.
    -- The ability to automatically parse Microsoft JSON Dates (AJAX Dates) was added in v9.5.0.76
    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

    DECLARE @success int
    EXEC sp_OAMethod @json, 'Load', @success OUT, '{ "AchievementDate":"/Date(1540229468330-0500)/"}'

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

    EXEC sp_OAMethod @json, 'DateOf', @success OUT, 'AchievementDate', @dt
    IF @success <> 1
      BEGIN

        PRINT 'Unable to parse a date/time.'
        EXEC @hr = sp_OADestroy @json
        EXEC @hr = sp_OADestroy @dt
        RETURN
      END

    -- Show the date in different formats:
    DECLARE @bLocal int
    SELECT @bLocal = 1

    EXEC sp_OAMethod @dt, 'GetAsRfc822', @sTmp0 OUT, @bLocal
    PRINT 'RFC822: ' + @sTmp0

    EXEC sp_OAMethod @dt, 'GetAsTimestamp', @sTmp0 OUT, @bLocal
    PRINT 'Timestamp: ' + @sTmp0

    EXEC sp_OAMethod @dt, 'GetAsIso8601', @sTmp0 OUT, 'YYYY-MM-DD', @bLocal
    PRINT 'YYYY-MM-DD: ' + @sTmp0

    -- Get integer values for year, month, day, etc.
    DECLARE @dtObj int
    EXEC sp_OAMethod @dt, 'GetDtObj', @dtObj OUT, @bLocal

    EXEC sp_OAGetProperty @dtObj, 'Year', @iTmp0 OUT
    PRINT 'year: ' + @iTmp0

    EXEC sp_OAGetProperty @dtObj, 'Month', @iTmp0 OUT
    PRINT 'month: ' + @iTmp0

    EXEC sp_OAGetProperty @dtObj, 'Day', @iTmp0 OUT
    PRINT 'day: ' + @iTmp0

    EXEC sp_OAGetProperty @dtObj, 'Hour', @iTmp0 OUT
    PRINT 'hour: ' + @iTmp0

    EXEC sp_OAGetProperty @dtObj, 'Minute', @iTmp0 OUT
    PRINT 'minute: ' + @iTmp0

    EXEC sp_OAGetProperty @dtObj, 'Second', @iTmp0 OUT
    PRINT 'seconds: ' + @iTmp0

    EXEC @hr = sp_OADestroy @dtObj

    -- Sample output:
    -- RFC822: Mon, 22 Oct 2018 17:31:08 -0500
    -- Timestamp: 2018-10-22T17:31:08-05:00
    -- YYYY-MM-DD: 2018-10-22
    -- year: 2018
    -- month: 10
    -- day: 22
    -- hour: 17
    -- minute: 31
    -- seconds: 8

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


END
GO