ISDATE() equivalent for MySQL

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;

​x
 
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 xWHERE ISDATE(dt) = 1;​

but isdate is not valid in MySQL, what can I do to fix this?

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

 
WHERE STR_TO_DATE(dt, '%d,%m,%Y') IS NOT NULL​

Advertisement

Answer