| Date comparison [message #627801] |
Mon, 17 November 2014 03:56  |
pointers
Messages: 451
Registered: May 2008
|
Senior Member |
|
|
Hi,
I have a table "emp_details" where "doj" column is defined as NUMBER and stores date values in the yyyymmdd format (I completly oppose it, but it is an existing system)
and we have a packaged procedure which accepts input argument from emp_details.doj column and compares it with predefined date formats to find out what is the YYYY, MM and DD
for e.g. the package code does -
-- first comparison
BEGIN
SELECT TO_DATE(v_Date, 'MM/DD/YY')
INTO v_outValDate
FROM DUAL;
RETURN TRIM(TO_CHAR(v_outValDate, 'YYYY-MM-DD'));
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- second comparison
BEGIN
SELECT TO_DATE(v_Date, 'MM.DD.YY')
INTO v_outValDate
FROM DUAL;
RETURN TRIM(TO_CHAR(v_outValDate, 'YYYY-MM-DD'));
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
-- third comparison
BEGIN
SELECT TO_DATE(v_Date, 'DD/MM/YYYY')
INTO v_outValDate
FROM DUAL;
RETURN TRIM(TO_CHAR(v_outValDate, 'YYYY-MM-DD'));
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
But, for a given doj column value i.e. 20120522 (v_date above), it matches to third block of code (third comparison above) and the package returns YYYY=0522, MM=12 and DD=20 (which is completly wrong as per the application).
Now, I would like to know if we can explicitly force oracle to strictly match literal to literal, letter to letter, symbol to symbol.
Above, 20120522 should be compared to DD/MM/YYYY and should fail the comparison because there is no '/' in the input string.
Regards,
Pointers
|
|
|
|
|
|
|
|
| Re: Date comparison [message #627815 is a reply to message #627801] |
Mon, 17 November 2014 05:10  |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:SELECT TO_DATE(v_Date, 'MM.DD.YY')
INTO v_outValDate
FROM DUAL;
No need to use SQL to just make an assignation of an expression.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
