Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save the-liquid-metal/5965a394c84cd9a91d83c2d1d860e27d to your computer and use it in GitHub Desktop.
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.
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 ;
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