Created
December 7, 2023 17:28
-
-
Save the-liquid-metal/5965a394c84cd9a91d83c2d1d860e27d to your computer and use it in GitHub Desktop.
mysql stored procedure proper_date clean-up text field from unstructured text. valid mysql date remain intact, invalid date converted to null.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DELIMITER $$ | |
CREATE DEFINER=`root`@`localhost` FUNCTION `proper_date`(`value` TEXT) | |
RETURNS TEXT | |
LANGUAGE SQL | |
DETERMINISTIC | |
CONTAINS SQL | |
SQL SECURITY DEFINER | |
COMMENT '' | |
BEGIN | |
# preprocessing ---------------------------------------------------------------------------------------------------- | |
IF value IS NOT NULL THEN SET value = TRIM(value); | |
END IF; | |
# filtering out ---------------------------------------------------------------------------------------------------- | |
IF value IS NULL THEN RETURN null; | |
ELSEIF value NOT REGEXP '^\\d{1,2}[-/.]\\d{1,2}[-/.]\\d{4}( +\\d{1,2}:\\d{1,2}:\\d{1,2})?$' | |
AND value NOT REGEXP '^\\d{4}[-/.]\\d{1,2}[-/.]\\d{1,2}( +\\d{1,2}:\\d{1,2}:\\d{1,2})?$' | |
THEN RETURN null; | |
ELSEIF value REGEXP '^00?[-/.]00?[-/.]0{4}( +00?:00?:00?)?$' THEN RETURN null; | |
ELSEIF value REGEXP '^0{4}[-/.]00?[-/.]00?( +00?:00?:00?)?$' THEN RETURN null; | |
ELSEIF value REGEXP '^0?1[-/.]0?1[-/.]1900( +00?:00?:00?)?$' THEN RETURN null; | |
ELSEIF value REGEXP '^1900[-/.]0?1[-/.]0?1( +00?:00?:00?)?$' THEN RETURN null; | |
# determining format ----------------------------------------------------------------------------------------------- | |
ELSEIF value REGEXP '^\\d{4}' AND STR_TO_DATE(value, '%Y-%m-%d %T') THEN RETURN STR_TO_DATE(value, '%Y-%m-%d %T'); | |
ELSEIF value REGEXP '^\\d{4}' AND STR_TO_DATE(value, '%Y-%d-%m %T') THEN RETURN STR_TO_DATE(value, '%Y-%d-%m %T'); | |
ELSEIF value REGEXP '^\\d{4}' AND STR_TO_DATE(value, '%Y/%m/%d %T') THEN RETURN STR_TO_DATE(value, '%Y/%m/%d %T'); | |
ELSEIF value REGEXP '^\\d{4}' AND STR_TO_DATE(value, '%Y/%d/%m %T') THEN RETURN STR_TO_DATE(value, '%Y/%d/%m %T'); | |
ELSEIF value REGEXP '^\\d{4}' AND STR_TO_DATE(value, '%Y.%m.%d %T') THEN RETURN STR_TO_DATE(value, '%Y.%m.%d %T'); | |
ELSEIF value REGEXP '^\\d{4}' AND STR_TO_DATE(value, '%Y.%d.%m %T') THEN RETURN STR_TO_DATE(value, '%Y.%d.%m %T'); | |
ELSEIF STR_TO_DATE(value, '%d-%m-%Y %T') THEN RETURN STR_TO_DATE(value, '%d-%m-%Y %T'); | |
ELSEIF STR_TO_DATE(value, '%m-%d-%Y %T') THEN RETURN STR_TO_DATE(value, '%m-%d-%Y %T'); | |
ELSEIF STR_TO_DATE(value, '%d/%m/%Y %T') THEN RETURN STR_TO_DATE(value, '%d/%m/%Y %T'); | |
ELSEIF STR_TO_DATE(value, '%m/%d/%Y %T') THEN RETURN STR_TO_DATE(value, '%m/%d/%Y %T'); | |
ELSEIF STR_TO_DATE(value, '%d.%m.%Y %T') THEN RETURN STR_TO_DATE(value, '%d.%m.%Y %T'); | |
ELSEIF STR_TO_DATE(value, '%m.%d.%Y %T') THEN RETURN STR_TO_DATE(value, '%m.%d.%Y %T'); | |
# throw away anything else ----------------------------------------------------------------------------------------- | |
ELSE RETURN null; | |
END IF; | |
END $$ | |
DELIMITER ; | |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT | |
u.tgl AS awal, | |
IF(u.tgl REGEXP '----- \\w+ -----', '----------', tlm_hospital_farmasi.proper_date(u.tgl, 1)) AS nullable, | |
IF(u.tgl REGEXP '----- \\w+ -----', '----------', tlm_hospital_farmasi.proper_date(u.tgl, 0)) AS nonnull | |
FROM ( | |
SELECT '----- ERROR -----' AS tgl | |
UNION ALL SELECT NULL | |
UNION ALL SELECT '' | |
UNION ALL SELECT '0000-00-00 00:00:00' | |
UNION ALL SELECT '0000-00-00' | |
UNION ALL SELECT '1900-01-01 00:00:00' | |
UNION ALL SELECT '1900-01-01' | |
UNION ALL SELECT 'asdf' | |
UNION ALL SELECT 0 | |
UNION ALL SELECT 0.1 | |
UNION ALL SELECT '0' | |
UNION ALL SELECT '0.1' | |
# -------------------------------------------- | |
UNION ALL SELECT '0000-00-000 00:00:00' | |
UNION ALL SELECT '0000/00/000 00:00:00' | |
UNION ALL SELECT '0000.00.000 00:00:00' | |
UNION ALL SELECT '0000-000-00 00:00:00' | |
UNION ALL SELECT '0000/000/00 00:00:00' | |
UNION ALL SELECT '0000.000.00 00:00:00' | |
UNION ALL SELECT '00000-00-00 00:00:00' | |
UNION ALL SELECT '00000/00/00 00:00:00' | |
UNION ALL SELECT '00000.00.00 00:00:00' | |
UNION ALL SELECT '0000-00-00 00:00:0' | |
UNION ALL SELECT '0000/00/00 00:00:0' | |
UNION ALL SELECT '0000.00.00 00:00:0' | |
UNION ALL SELECT '0000-00-00 00:0:00' | |
UNION ALL SELECT '0000/00/00 00:0:00' | |
UNION ALL SELECT '0000.00.00 00:0:00' | |
UNION ALL SELECT '0000-00-00 0:00:00' | |
UNION ALL SELECT '0000/00/00 0:00:00' | |
UNION ALL SELECT '0000.00.00 0:00:00' | |
UNION ALL SELECT '0000-00-00 00:00:000' | |
UNION ALL SELECT '0000/00/00 00:00:000' | |
UNION ALL SELECT '0000.00.00 00:00:000' | |
UNION ALL SELECT '0000-00-00 00:000:00' | |
UNION ALL SELECT '0000/00/00 00:000:00' | |
UNION ALL SELECT '0000.00.00 00:000:00' | |
UNION ALL SELECT '0000-00-00 000:00:00' | |
UNION ALL SELECT '0000/00/00 000:00:00' | |
UNION ALL SELECT '0000.00.00 000:00:00' | |
UNION ALL SELECT '0000-00-000' | |
UNION ALL SELECT '0000/00/000' | |
UNION ALL SELECT '0000.00.000' | |
UNION ALL SELECT '0000-000-00' | |
UNION ALL SELECT '0000/000/00' | |
UNION ALL SELECT '0000.000.00' | |
UNION ALL SELECT '00000-00-00' | |
UNION ALL SELECT '00000/00/00' | |
UNION ALL SELECT '00000.00.00' | |
# -------------------------------------------- | |
UNION ALL SELECT '00-000-0000 00:00:00' | |
UNION ALL SELECT '00/000/0000 00:00:00' | |
UNION ALL SELECT '00.000.0000 00:00:00' | |
UNION ALL SELECT '000-00-0000 00:00:00' | |
UNION ALL SELECT '000/00/0000 00:00:00' | |
UNION ALL SELECT '000.00.0000 00:00:00' | |
UNION ALL SELECT '00-00-00000 00:00:00' | |
UNION ALL SELECT '00/00/00000 00:00:00' | |
UNION ALL SELECT '00.00.00000 00:00:00' | |
UNION ALL SELECT '00-00-0000 00:00:0' | |
UNION ALL SELECT '00/00/0000 00:00:0' | |
UNION ALL SELECT '00.00.0000 00:00:0' | |
UNION ALL SELECT '00-00-0000 00:0:00' | |
UNION ALL SELECT '00/00/0000 00:0:00' | |
UNION ALL SELECT '00.00.0000 00:0:00' | |
UNION ALL SELECT '00-00-0000 0:00:00' | |
UNION ALL SELECT '00/00/0000 0:00:00' | |
UNION ALL SELECT '00.00.0000 0:00:00' | |
UNION ALL SELECT '00-00-0000 00:00:000' | |
UNION ALL SELECT '00/00/0000 00:00:000' | |
UNION ALL SELECT '00.00.0000 00:00:000' | |
UNION ALL SELECT '00-00-0000 00:000:00' | |
UNION ALL SELECT '00/00/0000 00:000:00' | |
UNION ALL SELECT '00.00.0000 00:000:00' | |
UNION ALL SELECT '00-00-0000 000:00:00' | |
UNION ALL SELECT '00/00/0000 000:00:00' | |
UNION ALL SELECT '00.00.0000 000:00:00' | |
UNION ALL SELECT '00-000-0000' | |
UNION ALL SELECT '00/000/0000' | |
UNION ALL SELECT '00.000.0000' | |
UNION ALL SELECT '000-00-0000' | |
UNION ALL SELECT '000/00/0000' | |
UNION ALL SELECT '000.00.0000' | |
UNION ALL SELECT '00-00-00000' | |
UNION ALL SELECT '00/00/00000' | |
UNION ALL SELECT '00.00.00000' | |
# -------------------------------------------- | |
UNION ALL SELECT '1900-01-001 00:00:00' | |
UNION ALL SELECT '1900/01/001 00:00:00' | |
UNION ALL SELECT '1900.01.001 00:00:00' | |
UNION ALL SELECT '1900-001-01 00:00:00' | |
UNION ALL SELECT '1900/001/01 00:00:00' | |
UNION ALL SELECT '1900.001.01 00:00:00' | |
UNION ALL SELECT '01900-01-01 00:00:00' | |
UNION ALL SELECT '01900/01/01 00:00:00' | |
UNION ALL SELECT '01900.01.01 00:00:00' | |
UNION ALL SELECT '1900-01-01 00:00:0' | |
UNION ALL SELECT '1900/01/01 00:00:0' | |
UNION ALL SELECT '1900.01.01 00:00:0' | |
UNION ALL SELECT '1900-01-01 00:0:00' | |
UNION ALL SELECT '1900/01/01 00:0:00' | |
UNION ALL SELECT '1900.01.01 00:0:00' | |
UNION ALL SELECT '1900-01-01 0:00:00' | |
UNION ALL SELECT '1900/01/01 0:00:00' | |
UNION ALL SELECT '1900.01.01 0:00:00' | |
UNION ALL SELECT '1900-01-01 00:00:000' | |
UNION ALL SELECT '1900/01/01 00:00:000' | |
UNION ALL SELECT '1900.01.01 00:00:000' | |
UNION ALL SELECT '1900-01-01 00:000:00' | |
UNION ALL SELECT '1900/01/01 00:000:00' | |
UNION ALL SELECT '1900.01.01 00:000:00' | |
UNION ALL SELECT '1900-01-01 000:00:00' | |
UNION ALL SELECT '1900/01/01 000:00:00' | |
UNION ALL SELECT '1900.01.01 000:00:00' | |
UNION ALL SELECT '1900-01-001' | |
UNION ALL SELECT '1900/01/001' | |
UNION ALL SELECT '1900.01.001' | |
UNION ALL SELECT '1900-001-01' | |
UNION ALL SELECT '1900/001/01' | |
UNION ALL SELECT '1900.001.01' | |
UNION ALL SELECT '01900-01-01' | |
UNION ALL SELECT '01900/01/01' | |
UNION ALL SELECT '01900.01.01' | |
# -------------------------------------------- | |
UNION ALL SELECT '01-001-1900 00:00:00' | |
UNION ALL SELECT '01/001/1900 00:00:00' | |
UNION ALL SELECT '01.001.1900 00:00:00' | |
UNION ALL SELECT '001-01-1900 00:00:00' | |
UNION ALL SELECT '001/01/1900 00:00:00' | |
UNION ALL SELECT '001.01.1900 00:00:00' | |
UNION ALL SELECT '01-01-01900 00:00:00' | |
UNION ALL SELECT '01/01/01900 00:00:00' | |
UNION ALL SELECT '01.01.01900 00:00:00' | |
UNION ALL SELECT '01-01-1900 00:00:0' | |
UNION ALL SELECT '01/01/1900 00:00:0' | |
UNION ALL SELECT '01.01.1900 00:00:0' | |
UNION ALL SELECT '01-01-1900 00:0:00' | |
UNION ALL SELECT '01/01/1900 00:0:00' | |
UNION ALL SELECT '01.01.1900 00:0:00' | |
UNION ALL SELECT '01-01-1900 0:00:00' | |
UNION ALL SELECT '01/01/1900 0:00:00' | |
UNION ALL SELECT '01.01.1900 0:00:00' | |
UNION ALL SELECT '01-01-1900 00:00:000' | |
UNION ALL SELECT '01/01/1900 00:00:000' | |
UNION ALL SELECT '01.01.1900 00:00:000' | |
UNION ALL SELECT '01-01-1900 00:000:00' | |
UNION ALL SELECT '01/01/1900 00:000:00' | |
UNION ALL SELECT '01.01.1900 00:000:00' | |
UNION ALL SELECT '01-01-1900 000:00:00' | |
UNION ALL SELECT '01/01/1900 000:00:00' | |
UNION ALL SELECT '01.01.1900 000:00:00' | |
UNION ALL SELECT '01-001-1900' | |
UNION ALL SELECT '01/001/1900' | |
UNION ALL SELECT '01.001.1900' | |
UNION ALL SELECT '001-01-1900' | |
UNION ALL SELECT '001/01/1900' | |
UNION ALL SELECT '001.01.1900' | |
UNION ALL SELECT '01-01-01900' | |
UNION ALL SELECT '01/01/01900' | |
UNION ALL SELECT '01.01.01900' | |
# ===================================== | |
UNION ALL SELECT '----- TRIM_EFFECT -----' | |
UNION ALL SELECT ' 2011-12-13 01:02:03 ' | |
UNION ALL SELECT ' 2011-12-13 ' | |
UNION ALL SELECT '* 2011-12-13 01:02:03 *' | |
UNION ALL SELECT '* 2011-12-13 *' | |
# ===================================== | |
UNION ALL SELECT '----- OK1 -----' | |
# OK1 1.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-13 01:02:03' | |
UNION ALL SELECT '2011-12-13' | |
UNION ALL SELECT '2011-13-12 01:02:03' | |
UNION ALL SELECT '2011-13-12' | |
# OK1 1.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/13 01:02:03' | |
UNION ALL SELECT '2011/12/13' | |
UNION ALL SELECT '2011/13/12 01:02:03' | |
UNION ALL SELECT '2011/13/12' | |
# OK1 1.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.13 01:02:03' | |
UNION ALL SELECT '2011.12.13' | |
UNION ALL SELECT '2011.13.12 01:02:03' | |
UNION ALL SELECT '2011.13.12' | |
# OK1 2.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-3 01:02:03' | |
UNION ALL SELECT '2011-12-3' | |
UNION ALL SELECT '2011-3-12 01:02:03' | |
UNION ALL SELECT '2011-3-12' | |
# OK1 2.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/3 01:02:03' | |
UNION ALL SELECT '2011/12/3' | |
UNION ALL SELECT '2011/3/12 01:02:03' | |
UNION ALL SELECT '2011/3/12' | |
# OK1 2.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.3 01:02:03' | |
UNION ALL SELECT '2011.12.3' | |
UNION ALL SELECT '2011.3.12 01:02:03' | |
UNION ALL SELECT '2011.3.12' | |
# OK1 3.1 ------------------------------------- | |
UNION ALL SELECT '2011-2-13 01:02:03' | |
UNION ALL SELECT '2011-2-13' | |
UNION ALL SELECT '2011-13-2 01:02:03' | |
UNION ALL SELECT '2011-13-2' | |
# OK1 3.2 ------------------------------------- | |
UNION ALL SELECT '2011/2/13 01:02:03' | |
UNION ALL SELECT '2011/2/13' | |
UNION ALL SELECT '2011/13/2 01:02:03' | |
UNION ALL SELECT '2011/13/2' | |
# OK1 3.3 ------------------------------------- | |
UNION ALL SELECT '2011.2.13 01:02:03' | |
UNION ALL SELECT '2011.2.13' | |
UNION ALL SELECT '2011.13.2 01:02:03' | |
UNION ALL SELECT '2011.13.2' | |
# OK1 4.1 ------------------------------------- | |
UNION ALL SELECT '12-13-2011 01:02:03' | |
UNION ALL SELECT '12-13-2011' | |
UNION ALL SELECT '13-12-2011 01:02:03' | |
UNION ALL SELECT '13-12-2011' | |
# OK1 4.2 ------------------------------------- | |
UNION ALL SELECT '12/13/2011 01:02:03' | |
UNION ALL SELECT '12/13/2011' | |
UNION ALL SELECT '13/12/2011 01:02:03' | |
UNION ALL SELECT '13/12/2011' | |
# OK1 4.3 ------------------------------------- | |
UNION ALL SELECT '12.13.2011 01:02:03' | |
UNION ALL SELECT '12.13.2011' | |
UNION ALL SELECT '13.12.2011 01:02:03' | |
UNION ALL SELECT '13.12.2011' | |
# OK1 5.1 ------------------------------------- | |
UNION ALL SELECT '12-3-2011 01:02:03' | |
UNION ALL SELECT '12-3-2011' | |
UNION ALL SELECT '3-12-2011 01:02:03' | |
UNION ALL SELECT '3-12-2011' | |
# OK1 5.2 ------------------------------------- | |
UNION ALL SELECT '12/3/2011 01:02:03' | |
UNION ALL SELECT '12/3/2011' | |
UNION ALL SELECT '3/12/2011 01:02:03' | |
UNION ALL SELECT '3/12/2011' | |
# OK1 5.3 ------------------------------------- | |
UNION ALL SELECT '12.3.2011 01:02:03' | |
UNION ALL SELECT '12.3.2011' | |
UNION ALL SELECT '3.12.2011 01:02:03' | |
UNION ALL SELECT '3.12.2011' | |
# OK1 6.1 ------------------------------------- | |
UNION ALL SELECT '2-13-2011 01:02:03' | |
UNION ALL SELECT '2-13-2011' | |
UNION ALL SELECT '13-2-2011 01:02:03' | |
UNION ALL SELECT '13-2-2011' | |
# OK1 6.2 ------------------------------------- | |
UNION ALL SELECT '2/13/2011 01:02:03' | |
UNION ALL SELECT '2/13/2011' | |
UNION ALL SELECT '13/2/2011 01:02:03' | |
UNION ALL SELECT '13/2/2011' | |
# OK1 6.3 ------------------------------------- | |
UNION ALL SELECT '2.13.2011 01:02:03' | |
UNION ALL SELECT '2.13.2011' | |
UNION ALL SELECT '13.2.2011 01:02:03' | |
UNION ALL SELECT '13.2.2011' | |
# ===================================== | |
UNION ALL SELECT '----- OK2 -----' | |
# OK2 1.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-13 01:02:3' | |
UNION ALL SELECT '2011-12-13 01:2:03' | |
UNION ALL SELECT '2011-12-13 1:02:03' | |
UNION ALL SELECT '2011-13-12 01:02:3' | |
UNION ALL SELECT '2011-13-12 01:2:03' | |
UNION ALL SELECT '2011-13-12 1:02:03' | |
# OK2 1.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/13 01:02:3' | |
UNION ALL SELECT '2011/12/13 01:2:03' | |
UNION ALL SELECT '2011/12/13 1:02:03' | |
UNION ALL SELECT '2011/13/12 01:02:3' | |
UNION ALL SELECT '2011/13/12 01:2:03' | |
UNION ALL SELECT '2011/13/12 1:02:03' | |
# OK2 1.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.13 01:02:3' | |
UNION ALL SELECT '2011.12.13 01:2:03' | |
UNION ALL SELECT '2011.12.13 1:02:03' | |
UNION ALL SELECT '2011.13.12 01:02:3' | |
UNION ALL SELECT '2011.13.12 01:2:03' | |
UNION ALL SELECT '2011.13.12 1:02:03' | |
# OK2 2.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-3 01:02:3' | |
UNION ALL SELECT '2011-12-3 01:2:03' | |
UNION ALL SELECT '2011-12-3 1:02:03' | |
UNION ALL SELECT '2011-3-12 01:02:3' | |
UNION ALL SELECT '2011-3-12 01:2:03' | |
UNION ALL SELECT '2011-3-12 1:02:03' | |
# OK2 2.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/3 01:02:3' | |
UNION ALL SELECT '2011/12/3 01:2:03' | |
UNION ALL SELECT '2011/12/3 1:02:03' | |
UNION ALL SELECT '2011/3/12 01:02:3' | |
UNION ALL SELECT '2011/3/12 01:2:03' | |
UNION ALL SELECT '2011/3/12 1:02:03' | |
# OK2 2.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.3 01:02:3' | |
UNION ALL SELECT '2011.12.3 01:2:03' | |
UNION ALL SELECT '2011.12.3 1:02:03' | |
UNION ALL SELECT '2011.3.12 01:02:3' | |
UNION ALL SELECT '2011.3.12 01:2:03' | |
UNION ALL SELECT '2011.3.12 1:02:03' | |
# OK2 3.1 ------------------------------------- | |
UNION ALL SELECT '2011-2-13 01:02:3' | |
UNION ALL SELECT '2011-2-13 01:2:03' | |
UNION ALL SELECT '2011-2-13 1:02:03' | |
UNION ALL SELECT '2011-13-2 01:02:3' | |
UNION ALL SELECT '2011-13-2 01:2:03' | |
UNION ALL SELECT '2011-13-2 1:02:03' | |
# OK2 3.2 ------------------------------------- | |
UNION ALL SELECT '2011/2/13 01:02:3' | |
UNION ALL SELECT '2011/2/13 01:2:03' | |
UNION ALL SELECT '2011/2/13 1:02:03' | |
UNION ALL SELECT '2011/13/2 01:02:3' | |
UNION ALL SELECT '2011/13/2 01:2:03' | |
UNION ALL SELECT '2011/13/2 1:02:03' | |
# OK2 3.3 ------------------------------------- | |
UNION ALL SELECT '2011.2.13 01:02:3' | |
UNION ALL SELECT '2011.2.13 01:2:03' | |
UNION ALL SELECT '2011.2.13 1:02:03' | |
UNION ALL SELECT '2011.13.2 01:02:3' | |
UNION ALL SELECT '2011.13.2 01:2:03' | |
UNION ALL SELECT '2011.13.2 1:02:03' | |
# OK2 4.1 ------------------------------------- | |
UNION ALL SELECT '12-13-2011 01:02:3' | |
UNION ALL SELECT '12-13-2011 01:2:03' | |
UNION ALL SELECT '12-13-2011 1:02:03' | |
UNION ALL SELECT '13-12-2011 01:02:3' | |
UNION ALL SELECT '13-12-2011 01:2:03' | |
UNION ALL SELECT '13-12-2011 1:02:03' | |
# OK2 4.2 ------------------------------------- | |
UNION ALL SELECT '12/13/2011 01:02:3' | |
UNION ALL SELECT '12/13/2011 01:2:03' | |
UNION ALL SELECT '12/13/2011 1:02:03' | |
UNION ALL SELECT '13/12/2011 01:02:3' | |
UNION ALL SELECT '13/12/2011 01:2:03' | |
UNION ALL SELECT '13/12/2011 1:02:03' | |
# OK2 4.3 ------------------------------------- | |
UNION ALL SELECT '12.13.2011 01:02:3' | |
UNION ALL SELECT '12.13.2011 01:2:03' | |
UNION ALL SELECT '12.13.2011 1:02:03' | |
UNION ALL SELECT '13.12.2011 01:02:3' | |
UNION ALL SELECT '13.12.2011 01:2:03' | |
UNION ALL SELECT '13.12.2011 1:02:03' | |
# OK2 5.1 ------------------------------------- | |
UNION ALL SELECT '12-3-2011 01:02:3' | |
UNION ALL SELECT '12-3-2011 01:2:03' | |
UNION ALL SELECT '12-3-2011 1:02:03' | |
UNION ALL SELECT '3-12-2011 01:02:3' | |
UNION ALL SELECT '3-12-2011 01:2:03' | |
UNION ALL SELECT '3-12-2011 1:02:03' | |
# OK2 5.2 ------------------------------------- | |
UNION ALL SELECT '12/3/2011 01:02:3' | |
UNION ALL SELECT '12/3/2011 01:2:03' | |
UNION ALL SELECT '12/3/2011 1:02:03' | |
UNION ALL SELECT '3/12/2011 01:02:3' | |
UNION ALL SELECT '3/12/2011 01:2:03' | |
UNION ALL SELECT '3/12/2011 1:02:03' | |
# OK2 5.3 ------------------------------------- | |
UNION ALL SELECT '12.3.2011 01:02:3' | |
UNION ALL SELECT '12.3.2011 01:2:03' | |
UNION ALL SELECT '12.3.2011 1:02:03' | |
UNION ALL SELECT '3.12.2011 01:02:3' | |
UNION ALL SELECT '3.12.2011 01:2:03' | |
UNION ALL SELECT '3.12.2011 1:02:03' | |
# OK2 6.1 ------------------------------------- | |
UNION ALL SELECT '2-13-2011 01:02:3' | |
UNION ALL SELECT '2-13-2011 01:2:03' | |
UNION ALL SELECT '2-13-2011 1:02:03' | |
UNION ALL SELECT '13-2-2011 01:02:3' | |
UNION ALL SELECT '13-2-2011 01:2:03' | |
UNION ALL SELECT '13-2-2011 1:02:03' | |
# OK2 6.2 ------------------------------------- | |
UNION ALL SELECT '2/13/2011 01:02:3' | |
UNION ALL SELECT '2/13/2011 01:2:03' | |
UNION ALL SELECT '2/13/2011 1:02:03' | |
UNION ALL SELECT '13/2/2011 01:02:3' | |
UNION ALL SELECT '13/2/2011 01:2:03' | |
UNION ALL SELECT '13/2/2011 1:02:03' | |
# OK2 6.3 ------------------------------------- | |
UNION ALL SELECT '2.13.2011 01:02:3' | |
UNION ALL SELECT '2.13.2011 01:2:03' | |
UNION ALL SELECT '2.13.2011 1:02:03' | |
UNION ALL SELECT '13.2.2011 01:02:3' | |
UNION ALL SELECT '13.2.2011 01:2:03' | |
UNION ALL SELECT '13.2.2011 1:02:03' | |
# ===================================== | |
UNION ALL SELECT '----- ERROR1 -----' | |
# ER1 1.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-013 01:02:03' | |
UNION ALL SELECT '2011-12-013' | |
UNION ALL SELECT '2011-013-12 01:02:03' | |
UNION ALL SELECT '2011-013-12' | |
UNION ALL SELECT '02011-13-12 01:02:03' | |
UNION ALL SELECT '02011-13-12' | |
# ER1 1.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/013 01:02:03' | |
UNION ALL SELECT '2011/12/013' | |
UNION ALL SELECT '2011/013/12 01:02:03' | |
UNION ALL SELECT '2011/013/12' | |
UNION ALL SELECT '02011/13/12 01:02:03' | |
UNION ALL SELECT '02011/13/12' | |
# ER1 1.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.013 01:02:03' | |
UNION ALL SELECT '2011.12.013' | |
UNION ALL SELECT '2011.013.12 01:02:03' | |
UNION ALL SELECT '2011.013.12' | |
UNION ALL SELECT '02011.13.12 01:02:03' | |
UNION ALL SELECT '02011.13.12' | |
# ER1 2.1 ------------------------------------- | |
UNION ALL SELECT '2011-012-3 01:02:03' | |
UNION ALL SELECT '2011-012-3' | |
UNION ALL SELECT '2011-3-012 01:02:03' | |
UNION ALL SELECT '2011-3-012' | |
UNION ALL SELECT '02011-3-12 01:02:03' | |
UNION ALL SELECT '02011-3-12' | |
# ER1 2.2 ------------------------------------- | |
UNION ALL SELECT '2011/012/3 01:02:03' | |
UNION ALL SELECT '2011/012/3' | |
UNION ALL SELECT '2011/3/012 01:02:03' | |
UNION ALL SELECT '2011/3/012' | |
UNION ALL SELECT '02011/3/12 01:02:03' | |
UNION ALL SELECT '02011/3/12' | |
# ER1 2.3 ------------------------------------- | |
UNION ALL SELECT '2011.012.3 01:02:03' | |
UNION ALL SELECT '2011.012.3' | |
UNION ALL SELECT '2011.3.012 01:02:03' | |
UNION ALL SELECT '2011.3.012' | |
UNION ALL SELECT '02011.3.012 01:02:03' | |
UNION ALL SELECT '02011.3.012' | |
# ER1 3.1 ------------------------------------- | |
UNION ALL SELECT '2011-2-013 01:02:03' | |
UNION ALL SELECT '2011-2-013' | |
UNION ALL SELECT '2011-013-2 01:02:03' | |
UNION ALL SELECT '2011-013-2' | |
UNION ALL SELECT '02011-13-2 01:02:03' | |
UNION ALL SELECT '02011-13-2' | |
# ER1 3.2 ------------------------------------- | |
UNION ALL SELECT '2011/2/013 01:02:03' | |
UNION ALL SELECT '2011/2/013' | |
UNION ALL SELECT '2011/013/2 01:02:03' | |
UNION ALL SELECT '2011/013/2' | |
UNION ALL SELECT '02011/13/2 01:02:03' | |
UNION ALL SELECT '02011/13/2' | |
# ER1 3.3 ------------------------------------- | |
UNION ALL SELECT '2011.2.013 01:02:03' | |
UNION ALL SELECT '2011.2.013' | |
UNION ALL SELECT '2011.013.2 01:02:03' | |
UNION ALL SELECT '2011.013.2' | |
UNION ALL SELECT '02011.13.2 01:02:03' | |
UNION ALL SELECT '02011.13.2' | |
# ER1 4.1 ------------------------------------- | |
UNION ALL SELECT '12-130-2011 01:02:03' | |
UNION ALL SELECT '12-130-2011' | |
UNION ALL SELECT '130-12-2011 01:02:03' | |
UNION ALL SELECT '130-12-2011' | |
UNION ALL SELECT '13-12-02011 01:02:03' | |
UNION ALL SELECT '13-12-02011' | |
# ER1 4.2 ------------------------------------- | |
UNION ALL SELECT '12/013/2011 01:02:03' | |
UNION ALL SELECT '12/013/2011' | |
UNION ALL SELECT '013/12/2011 01:02:03' | |
UNION ALL SELECT '013/12/2011' | |
UNION ALL SELECT '13/12/02011 01:02:03' | |
UNION ALL SELECT '13/12/02011' | |
# ER1 4.3 ------------------------------------- | |
UNION ALL SELECT '12.013.2011 01:02:03' | |
UNION ALL SELECT '12.013.2011' | |
UNION ALL SELECT '013.12.2011 01:02:03' | |
UNION ALL SELECT '013.12.2011' | |
UNION ALL SELECT '13.12.02011 01:02:03' | |
UNION ALL SELECT '13.12.02011' | |
# ER1 5.1 ------------------------------------- | |
UNION ALL SELECT '012-3-2011 01:02:03' | |
UNION ALL SELECT '012-3-2011' | |
UNION ALL SELECT '3-012-2011 01:02:03' | |
UNION ALL SELECT '3-012-2011' | |
UNION ALL SELECT '3-12-02011 01:02:03' | |
UNION ALL SELECT '3-12-02011' | |
# ER1 5.2 ------------------------------------- | |
UNION ALL SELECT '012/3/2011 01:02:03' | |
UNION ALL SELECT '012/3/2011' | |
UNION ALL SELECT '3/012/2011 01:02:03' | |
UNION ALL SELECT '3/012/2011' | |
UNION ALL SELECT '3/12/02011 01:02:03' | |
UNION ALL SELECT '3/12/02011' | |
# ER1 5.3 ------------------------------------- | |
UNION ALL SELECT '012.3.2011 01:02:03' | |
UNION ALL SELECT '012.3.2011' | |
UNION ALL SELECT '3.012.2011 01:02:03' | |
UNION ALL SELECT '3.012.2011' | |
UNION ALL SELECT '3.12.02011 01:02:03' | |
UNION ALL SELECT '3.12.02011' | |
# ER1 6.1 ------------------------------------- | |
UNION ALL SELECT '2-013-2011 01:02:03' | |
UNION ALL SELECT '2-013-2011' | |
UNION ALL SELECT '013-2-2011 01:02:03' | |
UNION ALL SELECT '013-2-2011' | |
UNION ALL SELECT '13-2-02011 01:02:03' | |
UNION ALL SELECT '13-2-02011' | |
# ER1 6.2 ------------------------------------- | |
UNION ALL SELECT '2/013/2011 01:02:03' | |
UNION ALL SELECT '2/013/2011' | |
UNION ALL SELECT '013/2/2011 01:02:03' | |
UNION ALL SELECT '013/2/2011' | |
UNION ALL SELECT '13/2/02011 01:02:03' | |
UNION ALL SELECT '13/2/02011' | |
# ER1 6.3 ------------------------------------- | |
UNION ALL SELECT '2.013.2011 01:02:03' | |
UNION ALL SELECT '2.013.2011' | |
UNION ALL SELECT '013.2.2011 01:02:03' | |
UNION ALL SELECT '013.2.2011' | |
UNION ALL SELECT '13.2.02011 01:02:03' | |
UNION ALL SELECT '13.2.02011' | |
# ===================================== | |
UNION ALL SELECT '----- ERROR2 -----' | |
# ER2 1.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-99 01:02:03' | |
UNION ALL SELECT '2011-12-99' | |
UNION ALL SELECT '2011-99-12 01:02:03' | |
UNION ALL SELECT '2011-99-12' | |
# ER2 1.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/99 01:02:03' | |
UNION ALL SELECT '2011/12/99' | |
UNION ALL SELECT '2011/99/12 01:02:03' | |
UNION ALL SELECT '2011/99/12' | |
# ER2 1.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.99 01:02:03' | |
UNION ALL SELECT '2011.12.99' | |
UNION ALL SELECT '2011.99.12 01:02:03' | |
UNION ALL SELECT '2011.99.12' | |
# ER2 2.1 ------------------------------------- | |
UNION ALL SELECT '2011-99-3 01:02:03' | |
UNION ALL SELECT '2011-99-3' | |
UNION ALL SELECT '2011-3-99 01:02:03' | |
UNION ALL SELECT '2011-3-99' | |
# ER2 2.2 ------------------------------------- | |
UNION ALL SELECT '2011/99/3 01:02:03' | |
UNION ALL SELECT '2011/99/3' | |
UNION ALL SELECT '2011/3/99 01:02:03' | |
UNION ALL SELECT '2011/3/99' | |
# ER2 2.3 ------------------------------------- | |
UNION ALL SELECT '2011.99.3 01:02:03' | |
UNION ALL SELECT '2011.99.3' | |
UNION ALL SELECT '2011.3.99 01:02:03' | |
UNION ALL SELECT '2011.3.99' | |
# ER2 3.1 ------------------------------------- | |
UNION ALL SELECT '2011-2-99 01:02:03' | |
UNION ALL SELECT '2011-2-99' | |
UNION ALL SELECT '2011-99-2 01:02:03' | |
UNION ALL SELECT '2011-99-2' | |
# ER2 3.2 ------------------------------------- | |
UNION ALL SELECT '2011/2/99 01:02:03' | |
UNION ALL SELECT '2011/2/99' | |
UNION ALL SELECT '2011/99/2 01:02:03' | |
UNION ALL SELECT '2011/99/2' | |
# ER2 3.3 ------------------------------------- | |
UNION ALL SELECT '2011.2.99 01:02:03' | |
UNION ALL SELECT '2011.2.99' | |
UNION ALL SELECT '2011.99.2 01:02:03' | |
UNION ALL SELECT '2011.99.2' | |
# ER2 4.1 ------------------------------------- | |
UNION ALL SELECT '12-99-2011 01:02:03' | |
UNION ALL SELECT '12-99-2011' | |
UNION ALL SELECT '99-12-2011 01:02:03' | |
UNION ALL SELECT '99-12-2011' | |
# ER2 4.2 ------------------------------------- | |
UNION ALL SELECT '12/99/2011 01:02:03' | |
UNION ALL SELECT '12/99/2011' | |
UNION ALL SELECT '99/12/2011 01:02:03' | |
UNION ALL SELECT '99/12/2011' | |
# ER2 4.3 ------------------------------------- | |
UNION ALL SELECT '12.99.2011 01:02:03' | |
UNION ALL SELECT '12.99.2011' | |
UNION ALL SELECT '99.12.2011 01:02:03' | |
UNION ALL SELECT '99.12.2011' | |
# ER2 5.1 ------------------------------------- | |
UNION ALL SELECT '99-3-2011 01:02:03' | |
UNION ALL SELECT '99-3-2011' | |
UNION ALL SELECT '3-99-2011 01:02:03' | |
UNION ALL SELECT '3-99-2011' | |
# ER2 5.2 ------------------------------------- | |
UNION ALL SELECT '99/3/2011 01:02:03' | |
UNION ALL SELECT '99/3/2011' | |
UNION ALL SELECT '3/99/2011 01:02:03' | |
UNION ALL SELECT '3/99/2011' | |
# ER2 5.3 ------------------------------------- | |
UNION ALL SELECT '99.3.2011 01:02:03' | |
UNION ALL SELECT '99.3.2011' | |
UNION ALL SELECT '3.99.2011 01:02:03' | |
UNION ALL SELECT '3.99.2011' | |
# ER2 6.1 ------------------------------------- | |
UNION ALL SELECT '2-99-2011 01:02:03' | |
UNION ALL SELECT '2-99-2011' | |
UNION ALL SELECT '99-2-2011 01:02:03' | |
UNION ALL SELECT '99-2-2011' | |
# ER2 6.2 ------------------------------------- | |
UNION ALL SELECT '2/99/2011 01:02:03' | |
UNION ALL SELECT '2/99/2011' | |
UNION ALL SELECT '99/2/2011 01:02:03' | |
UNION ALL SELECT '99/2/2011' | |
# ER2 6.3 ------------------------------------- | |
UNION ALL SELECT '2.99.2011 01:02:03' | |
UNION ALL SELECT '2.99.2011' | |
UNION ALL SELECT '99.2.2011 01:02:03' | |
UNION ALL SELECT '99.2.2011' | |
# ===================================== | |
UNION ALL SELECT '----- ERROR3 -----' | |
# ER3 1.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-13 01:02:003' | |
UNION ALL SELECT '2011-13-12 01:002:03' | |
UNION ALL SELECT '2011-13-12 001:02:03' | |
# ER3 1.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/13 01:02:003' | |
UNION ALL SELECT '2011/13/12 01:002:03' | |
UNION ALL SELECT '2011/13/12 001:02:03' | |
# ER3 1.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.13 01:02:003' | |
UNION ALL SELECT '2011.13.12 01:002:03' | |
UNION ALL SELECT '2011.13.12 001:02:03' | |
# ER3 2.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-3 01:02:003' | |
UNION ALL SELECT '2011-3-12 01:002:03' | |
UNION ALL SELECT '2011-3-12 001:02:03' | |
# ER3 2.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/3 01:02:003' | |
UNION ALL SELECT '2011/3/12 01:002:03' | |
UNION ALL SELECT '2011/3/12 001:02:03' | |
# ER3 2.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.3 01:02:003' | |
UNION ALL SELECT '2011.3.12 01:002:03' | |
UNION ALL SELECT '2011.3.12 001:02:03' | |
# ER3 3.1 ------------------------------------- | |
UNION ALL SELECT '2011-2-13 01:02:003' | |
UNION ALL SELECT '2011-13-2 01:002:03' | |
UNION ALL SELECT '2011-13-2 001:02:03' | |
# ER3 3.2 ------------------------------------- | |
UNION ALL SELECT '2011/2/13 01:02:003' | |
UNION ALL SELECT '2011/13/2 01:002:03' | |
UNION ALL SELECT '2011/13/2 001:02:03' | |
# ER3 3.3 ------------------------------------- | |
UNION ALL SELECT '2011.2.13 01:02:003' | |
UNION ALL SELECT '2011.13.2 01:002:03' | |
UNION ALL SELECT '2011.13.2 001:02:03' | |
# ER3 4.1 ------------------------------------- | |
UNION ALL SELECT '12-13-2011 01:02:003' | |
UNION ALL SELECT '13-12-2011 01:002:03' | |
UNION ALL SELECT '13-12-2011 001:02:03' | |
# ER3 4.2 ------------------------------------- | |
UNION ALL SELECT '12/13/2011 01:02:003' | |
UNION ALL SELECT '13/12/2011 01:002:03' | |
UNION ALL SELECT '13/12/2011 001:02:03' | |
# ER3 4.3 ------------------------------------- | |
UNION ALL SELECT '12.13.2011 01:02:003' | |
UNION ALL SELECT '13.12.2011 01:002:03' | |
UNION ALL SELECT '13.12.2011 001:02:03' | |
# ER3 5.1 ------------------------------------- | |
UNION ALL SELECT '12-3-2011 01:02:003' | |
UNION ALL SELECT '3-12-2011 01:002:03' | |
UNION ALL SELECT '3-12-2011 001:02:03' | |
# ER3 5.2 ------------------------------------- | |
UNION ALL SELECT '12/3/2011 01:02:003' | |
UNION ALL SELECT '3/12/2011 01:002:03' | |
UNION ALL SELECT '3/12/2011 001:02:03' | |
# ER3 5.3 ------------------------------------- | |
UNION ALL SELECT '12.3.2011 01:02:003' | |
UNION ALL SELECT '3.12.2011 01:002:03' | |
UNION ALL SELECT '3.12.2011 001:02:03' | |
# ER3 6.1 ------------------------------------- | |
UNION ALL SELECT '2-13-2011 01:02:003' | |
UNION ALL SELECT '13-2-2011 01:002:03' | |
UNION ALL SELECT '13-2-2011 001:02:03' | |
# ER3 6.2 ------------------------------------- | |
UNION ALL SELECT '2/13/2011 01:02:003' | |
UNION ALL SELECT '13/2/2011 01:002:03' | |
UNION ALL SELECT '13/2/2011 001:02:03' | |
# ER3 6.3 ------------------------------------- | |
UNION ALL SELECT '2.13.2011 01:02:003' | |
UNION ALL SELECT '13.2.2011 01:002:03' | |
UNION ALL SELECT '13.2.2011 001:02:03' | |
# ===================================== | |
UNION ALL SELECT '----- ERROR4 -----' | |
# ER4 1.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-13 01:02:99' | |
UNION ALL SELECT '2011-13-12 01:99:03' | |
UNION ALL SELECT '2011-13-12 99:02:03' | |
# ER4 1.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/13 01:02:99' | |
UNION ALL SELECT '2011/13/12 01:99:03' | |
UNION ALL SELECT '2011/13/12 99:02:03' | |
# ER4 1.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.13 01:02:99' | |
UNION ALL SELECT '2011.13.12 01:99:03' | |
UNION ALL SELECT '2011.13.12 99:02:03' | |
# ER4 2.1 ------------------------------------- | |
UNION ALL SELECT '2011-12-3 01:02:99' | |
UNION ALL SELECT '2011-3-12 01:99:03' | |
UNION ALL SELECT '2011-3-12 99:02:03' | |
# ER4 2.2 ------------------------------------- | |
UNION ALL SELECT '2011/12/3 01:02:99' | |
UNION ALL SELECT '2011/3/12 01:99:03' | |
UNION ALL SELECT '2011/3/12 99:02:03' | |
# ER4 2.3 ------------------------------------- | |
UNION ALL SELECT '2011.12.3 01:02:99' | |
UNION ALL SELECT '2011.3.12 01:99:03' | |
UNION ALL SELECT '2011.3.12 99:02:03' | |
# ER4 3.1 ------------------------------------- | |
UNION ALL SELECT '2011-2-13 01:02:99' | |
UNION ALL SELECT '2011-13-2 01:99:03' | |
UNION ALL SELECT '2011-13-2 99:02:03' | |
# ER4 3.2 ------------------------------------- | |
UNION ALL SELECT '2011/2/13 01:02:99' | |
UNION ALL SELECT '2011/13/2 01:99:03' | |
UNION ALL SELECT '2011/13/2 99:02:03' | |
# ER4 3.3 ------------------------------------- | |
UNION ALL SELECT '2011.2.13 01:02:99' | |
UNION ALL SELECT '2011.13.2 01:99:03' | |
UNION ALL SELECT '2011.13.2 99:02:03' | |
# ER4 4.1 ------------------------------------- | |
UNION ALL SELECT '12-13-2011 01:02:99' | |
UNION ALL SELECT '13-12-2011 01:99:03' | |
UNION ALL SELECT '13-12-2011 99:02:03' | |
# ER4 4.2 ------------------------------------- | |
UNION ALL SELECT '12/13/2011 01:02:99' | |
UNION ALL SELECT '13/12/2011 01:99:03' | |
UNION ALL SELECT '13/12/2011 99:02:03' | |
# ER4 4.3 ------------------------------------- | |
UNION ALL SELECT '12.13.2011 01:02:99' | |
UNION ALL SELECT '13.12.2011 01:99:03' | |
UNION ALL SELECT '13.12.2011 99:02:03' | |
# ER4 5.1 ------------------------------------- | |
UNION ALL SELECT '12-3-2011 01:02:99' | |
UNION ALL SELECT '3-12-2011 01:99:03' | |
UNION ALL SELECT '3-12-2011 99:02:03' | |
# ER4 5.2 ------------------------------------- | |
UNION ALL SELECT '12/3/2011 01:02:99' | |
UNION ALL SELECT '3/12/2011 01:99:03' | |
UNION ALL SELECT '3/12/2011 99:02:03' | |
# ER4 5.3 ------------------------------------- | |
UNION ALL SELECT '12.3.2011 01:02:99' | |
UNION ALL SELECT '3.12.2011 01:99:03' | |
UNION ALL SELECT '3.12.2011 99:02:03' | |
# ER4 6.1 ------------------------------------- | |
UNION ALL SELECT '2-13-2011 01:02:99' | |
UNION ALL SELECT '13-2-2011 01:99:03' | |
UNION ALL SELECT '13-2-2011 99:02:03' | |
# ER4 6.2 ------------------------------------- | |
UNION ALL SELECT '2/13/2011 01:02:99' | |
UNION ALL SELECT '13/2/2011 01:99:03' | |
UNION ALL SELECT '13/2/2011 99:02:03' | |
# ER4 6.3 ------------------------------------- | |
UNION ALL SELECT '2.13.2011 01:02:99' | |
UNION ALL SELECT '13.2.2011 01:99:03' | |
UNION ALL SELECT '13.2.2011 99:02:03' | |
) AS u; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment