I have a line of code for SQL server which takes a date listed as “YYYYMMDD” where the DD is 00 and converts the 00 to 01 so that it works with datetime. I would like to be able to use MySQL for it
the current code which works for SQL server:
INSERT patentdb.Citation(PatentNo, Citation, CitedBy, CitationDate) SELECT PatentNo, citation, WhoCitedThis, dt FROM ( SELECT PatentNo, Citation, WhoCitedThis, dt = CASE WHEN CitationDate LIKE '%00' THEN INSERT (CitationDate, 8, 1, '1') ELSE CitationDate END FROM patentdb.CitationSource ) AS x WHERE ISDATE(dt) = 1;
but isdate is not valid in MySQL, what can I do to fix this?
Advertisement
Answer
You can try using the STR_TO_DATE function. It returns null
if the expression is not date, time, or datetime.
WHERE STR_TO_DATE(dt, '%d,%m,%Y') IS NOT NULL