How to get date from JSON in correct format?
-
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? -
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) - Jeff Moden - Saturday, June 3, 2017 9:37 PM
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.
-
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 -
@Eirikur Eiriksson, Jeff Moden
Thanks very much! All solutions work perfectly!
-
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