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?
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
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With