I have a select query that consist an expression of TO_DATE(expiry_date, 'YYMM').
It works if expiry_date is in YYMM format.
I want to set data as 0001 if expiry_date is in incorrect formats that are like YYM, YY,MM,YMM etc.
Is this possible?
Well, this is ugly, but seems to do the trick:
WITH data AS (
SELECT '123' AS val FROM dual
UNION SELECT '12' FROM dual
UNION SELECT '1' FROM dual
UNION SELECT '12345' FROM dual
UNION SELECT '9612' FROM dual -- ok
UNION SELECT '9613' FROM dual -- wrong
UNION SELECT '1296' FROM dual -- wrong
UNION SELECT 'AAAA' FROM dual -- wrong
)
SELECT
CASE
WHEN length(val) != 4
OR NOT regexp_like(val, '[0-9]{4}')
OR NOT to_number(substr(val, 3, 2)) BETWEEN 1 AND 12
THEN to_date('0010', 'YYMM')
ELSE to_date(val, 'YYMM')
END AS date_yymm
FROM data;
Output:
DATE_YYMM ---------- 01-10-2000 01-10-2000 01-10-2000 01-10-2000 01-10-2000 01-12-2096 01-10-2000 01-10-2000
Or you can just use a function:
CREATE OR REPLACE FUNCTION is_date_format(p_value IN VARCHAR2, p_format IN VARCHAR2) RETURN NUMBER
AS
l_dummy DATE;
BEGIN
l_dummy := to_date(p_value, p_format);
RETURN 0;
EXCEPTION
WHEN OTHERS THEN
RETURN 1;
END;
/
WITH data AS (
SELECT '123' AS val FROM dual
UNION SELECT '12' FROM dual
UNION SELECT '1' FROM dual
UNION SELECT '12345' FROM dual
UNION SELECT '9612' FROM dual -- ok
UNION SELECT '9613' FROM dual -- wrong
UNION SELECT '1296' FROM dual -- wrong
)
SELECT
-- fx for exact matching as pointed out by Nicholas Krasnov
DECODE(is_date_format(val, 'fxYYMM'), 0, val, '0010')
FROM data;
SELECT CASE
WHEN LENGTH(expiry_date) = 4
THEN TO_DATE(expiry_date, 'YYMM')
ELSE TO_DATE('0001', 'YYMM')
END AS d_date
FROM
(
SELECT 1212 expiry_date FROM DUAL UNION ALL
SELECT 1211 FROM DUAL UNION ALL
SELECT 1210 FROM DUAL UNION ALL
SELECT 121 FROM DUAL UNION ALL
SELECT 12 FROM DUAL
)
;
/*
2012-12-01 00:00:00
2012-11-01 00:00:00
2012-10-01 00:00:00
2000-01-01 00:00:00
2000-01-01 00:00:00
*/
Something like this?
YYMorYMM?I want to set data as 0100. Sorry, you wont be able to do that, because month cannot be00- should befrom 01 to 12. So you have to pick another substitution value.