ISDATE() equivalent for MySQL

Question

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?

Abstriction

forums.mysql.com/read.php?10,35764,35780#msg-35780 – Icaria

possible duplicate of Validate Date at MySQL – Cherey

Answer 1

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

Affright

Error Code: 1582. Incorrect parameter count in the call to native function 'STR_TO_DATE' – Abstriction

Oh yes, I forgot the format parameter. Just add a date type format if you expect dt to have a date. I edited my answer. – Affright

Now it is throwing the following error Error Code: 1054. Unknown column 'dt' in 'field list' – Abstriction

Hmm, strange. I don't think it would just do that unless something was changed. The error msg sounds like the problem is dt in the select clause, not the where clause; maybe caused by something in the sub-query that created the field. Also this isn't relevant to the Q&A at hand (I should still have the accepted answer and if you can't figure out the problem, you should make a new post). But try appending the table name to it in the first select and the where; x.dt, and make sure the field is being created properly still in your sub-query. Good luck, and if you end up making a new post – Affright

...feel free to give me the link. – Affright

sorry I didnt mean to uncheck your answer. – Abstriction

It seems that this solution doesn't take care of cases when days or months have only 1 digit, these dates will be converted instead of returning null. Try this: SELECT STR_TO_DATE('5,1,2020', '%d,%m,%Y'). Additionally empty string or 0 values will be converted to '0000-00-00'. – Wadleigh

SELECT STR_TO_DATE(DATE_FORMAT('2021-05-21', '%d-%b-%y'), '%d-%b-%y'); valid date check – Snaggy

This solution unfortunately may cause problem in some circumstances. If STR_TO_DATE is wrapped into UDF it throws an error (or may throw under some circumstances, not sure). stackoverflow.com/questions/76198381 – Inimitable

Answer 2

One possibility, that allows argument to be string, integer or date:

WHERE DAYNAME(dt) IS NOT NULL

These valid dates return 'Tuesday':

SELECT IFNULL(DAYNAME('2016-06-21 18:17:47') , '');
SELECT IFNULL(DAYNAME('2016-06-21') , '');

These invalid dates return '' (empty string):

SELECT IFNULL(DAYNAME('0000-00-00 00:00:00') , '');
SELECT IFNULL(DAYNAME('2016-06-32 18:17:47') , '');
SELECT IFNULL(DAYNAME(NULL) , '');
SELECT IFNULL(DAYNAME(10) , '');

It seems that DAYNAME is 2x faster in mysql 5.6 than STR_TO_DATE:

SELECT benchmark(10000000, DAYNAME('2016-06-21 18:17:47'))
1 row(s) returned   3.215 sec / 0.0000072 sec

SELECT benchmark(10000000, STR_TO_DATE('2016-06-21 18:17:47', '%d,%m,%Y'))
1 row(s) returned   7.905 sec / 0.0000081 sec

And I suppose that if the argument is date (rather than eg. string), the performance is better.

Delanadelancey

Since this raises a warning ("Incorrect datetime value") if the date is invalid, it causes inserts and updates to fail. Is there any way to do something like INSERT INTO tbl (my_date) SELECT IF(DAYNAME(dt) IS NOT NULL, dt, '1900-01-01') FROM source_tbl? – Affectionate

Answer 3

Similar to Timo Kähkönen's answer, I've used TIMESTAMPDIFF to determine if a date is valid like ISDATE does. I use the same date in both date parameters. It returns zero if it a date, NULL if not.

I ran all three examples with BENCHMARK with valid and invalid dates. I ran this on a shared server from the ISP JustHost, MYSQL version 5.6.32-78.1:

SELECT benchmark(10000000, DAYNAME('2016-06-21 18:17:47'));
-- 1 row(s) returned   3.215 sec / 0.0000072 sec

Mine:  Query took 3.5333 seconds.

SELECT benchmark(10000000, STR_TO_DATE('2016-06-21 18:17:47', '%d,%m,%Y'));
-- 1 row(s) returned   7.905 sec / 0.0000081 sec

Mine: Query took 7.9635 seconds.

SELECT benchmark(10000000, TIMESTAMPDIFF(DAY,'2016-06-21 18:17:47','2016-06-21 18:17:47'));

Mine:  Query took 5.1373 seconds.

...........................

With bad date (June 41st?)

SELECT benchmark(10000000, DAYNAME('2016-06-41 18:17:47'));

Mine: Query took 7.3872 seconds.

SELECT benchmark(10000000, STR_TO_DATE('2016-06-41 18:17:47', '%d,%m,%Y'));

Mine: Query took 7.9919 seconds.

SELECT benchmark(10000000, TIMESTAMPDIFF(DAY,'2016-06-41 18:17:47','2016-06-41 18:17:47'));

Mine:  Query took 7.3792 seconds.

STR_TO_DATE is slightly slower than the other two. The DAYNAME method seems the fastest if you are working mostly with valid dates. But none is truly a bad way to go.

Tuition

Answer 4

You could also use the following

Using a REGEXP '^([1-9]|0[1-9]|1[012])/([1-9]|0[1-9]|[12][0-9]|3[01])/(19|20)[0-9][0-9]'

But regexp can be tricky here though, as there too many different format for a date,

Or

Cast DATE and then check for length of result, found null, then not date. length(DATE(mydate))

Millicent

That will not handle invalid dates correctly (e.g. 2012-02-31) – Tole

I understand the regexp is dodgy. What about the later with Date? Do you meant both are not working or which one? – Millicent

Answer 5

Many of the solutions I've seen allow invalid dates like '2023-02-30' or '2023-2-2'.

I use this combination of date functions and a simple regular expresssion to validate dates in Mysql/MariaDB:

SET @date = '2023-02-28';
SELECT DATE(@date) IS NOT NULL AND @date REGEXP '^[0-9]{4}-[0-9]{2}-[0-9]{2}$';

Result is 1

SET @date = '2023-02-29';

and result is 0

You may need to make some input adjustments if your date format is not YYYY-MM-DD:

SET @date = STR_TO_DATE('2/28/2023', '%m/%d/%Y');

Daladier

Answer 6

Well, while I appreciate all statements, I always like it the easy way.

All date stamps either have the "-" or "/" characters in them, so why not check all date columns that have such characters, using the LIKE argument.

SELECT * FROM TABLE WHERE DATE_COLUMN LIKE '%/%';
SELECT * FROM TABLE WHERE DATE_COLUMN LIKE '%-%';

Masters

This ain't too good of an idea. What would would happen if you checked a string that had / or - in it that wasn't a date? – Ixion