CODEWITHSUNDEEP
sqlsql-serverdatesubstring
Pan Markosian
Pan Markosian

Reputation: 101

SQL Substring after and before

I am trying to find miss typed dates and i have this json in my query

{"year":"2020","dateFrom":"2011-01-01T00:00:00Z","dateTo":"2011-12-31T00:00:00Z"}

  1. Substring only the "dateFrom":"2011-01-01T00:00:00Z
  2. Substring only the "dateTo":"2011-12-31T00:00:00Z
  3. Validate that date are correctly typed

Upvotes: 0

Views: 44

Answers (1)

Thom A
Thom A

Reputation: 96044

Seems the easiest method might be to parse the data as JSON, and then use TRY_CONVERT to make sure it the date valid. Then you can manually fix the bad data:

SELECT J.Year,
       dateFrom,
       dateTo
FROM dbo.YourTable YT
     CROSS APPLY OPENJSON(YT.YourJson)
                 WITH (year int, 
                       dateFrom varchar(30),
                       dateTo varchar(30)) J
WHERE TRY_CONVERT(datetimeoffset(0),dateFrom,127) IS NULL
   OR TRY_CONVERT(datetimeoffset(0),dateTo,127) IS NULL;

Upvotes: 2

Related Questions