SQL Server

Parse a Microsoft JSON Date (MS AJAX Date)

See more JSON Examples

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

-- 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)
    DECLARE @success int
    SELECT @success = 0

    -- Parse Microsoft JSON Dates (AJAX Dates)
    DECLARE @json int
    EXEC @hr = sp_OACreate 'Chilkat.JsonObject', @json OUT
    IF @hr <> 0
    BEGIN
        PRINT 'Failed to create ActiveX component'
        RETURN
    END

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

    DECLARE @dt int
    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 @hr = sp_OACreate 'Chilkat.DtObj', @dtObj OUT

    EXEC sp_OAMethod @dt, 'ToDtObj', NULL, @bLocal, @dtObj


    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

    -- 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
    EXEC @hr = sp_OADestroy @dtObj


END
GO