0

I have a file in json, but I have trouble separating the dates in the year-month, for instance, I have in SQL (AWS)

SELECT json_extract_scalar(json, '$.date') Date 
FROM table_json

Input:

 Date
 ------------------------
 2016-03-08T01:28:57.546Z
 2016-03-07T05:03:58.455Z
 ....
 2016-04-07T05:03:58.455Z
 2016-04-07T05:03:58.455Z
 2016-04-08T05:03:58.455Z
 2016-05-07T05:03:58.455Z
 ...

and I want to have something like

Date
--------
2016-03
2016-04
2016-05
....
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Pether Cll_
  • 11
  • 4
  • 1
    JSON does not have a Date/DateTime type. it's just a string, commonly stored as ISO8601. You need to parse that into a date and extract month + year. (depends on your sql type/dialect how to do that) – Marian Theisen Jan 06 '22 at 06:13
  • [Edit] the question and tag the DBMS you're using. – sticky bit Jan 06 '22 at 06:14
  • Ok, I am occupying the aws amazon platform, with sql. But how can extract motn + year? – Pether Cll_ Jan 06 '22 at 06:20
  • Please check if the following link answers your question https://stackoverflow.com/questions/59485913/how-to-extract-month-name-on-a-string-datatype-on-athena – Vineet Desai Jan 06 '22 at 06:37

0 Answers0