Post reply

How to get date from JSON in correct format?

  • June 3, 2017 at 11:25 am

    #321932

    Say, I have the following JSON and query:

    declare @json nvarchar(max) =
    '
    [
      {
       "JDate": "01.04.2017",
       "JName": "John"
      }
    ]
    ';

    select datepart(day, JDate) [day],
           datepart(month, JDate) [month],
           datepart(year, JDate) [year]
    from openjson(@json)
    with
    (
      JDate date,
      JName varchar(max)
    );

    The problem is that the date is April, 1-st, but SQL Server returns January, 4-th (i.e. U.S. format).
    Is there some way to get date in DAY/MONTH/YEAR format?

  • June 3, 2017 at 9:37 pm

    #1945283

    Can't test it because I'm no where near a 2016 box just now but if all the dates in your JSON are in the DMY format, then just add the following code just before the DECLARE and (no pun intended) you should be all set.

        SET DATEFORMAT DMY;

    The setting lasts only as long as the session and has only session scope.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • June 3, 2017 at 9:41 pm

    #1945284

    Jeff Moden - Saturday, June 3, 2017 9:37 PM

    Can't test it because I'm no where near a 2016 box just now but if all the dates in your JSON are in the DMY format, then just add the following code just before the DECLARE and (no pun intended) you should be all set.

        SET DATEFORMAT DMY;

    The setting lasts only as long as the session and has only session scope.

    That's what I was thinking of, but since I don't have to deal with it much, I wasn't certain.  I couldn't test it either and don't know the JSON function.

    Tally Tables - Performance Personified
    String Splitting with True Performance
    Best practices on how to ask questions

  • June 3, 2017 at 11:32 pm

    #1945289

    Here is a quick fix which is to remove the type cast from json
    😎

    declare @json nvarchar(max) =
    '
    [
    {
      "JDate": "01.04.2017",
     "JName": "John"
    }
    ]
    ';
    select
      CONVERT(DATE,JDate,104) AS THE_DATE
      ,datepart(day, CONVERT(DATE,JDate,104)) [day]
      ,datepart(month, CONVERT(DATE,JDate,104)) [month]
      ,datepart(year, CONVERT(DATE,JDate,104)) [year]
    from openjson(@json)
    with
    (
    JDate VARCHAR(10),
    JName varchar(max)
    );

    Output
    THE_DATE day month year
    ---------- ---- ------ -----
    2017-04-01 1  4  2017

  • June 4, 2017 at 2:31 am

    #1945295

    @Eirikur Eiriksson, Jeff Moden
    Thanks very much! All solutions work perfectly! 

  • June 4, 2017 at 10:22 am

    #1945308

    Thanks for the feedback! 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply