| Date Validation [message #371646] |
Mon, 20 November 2000 09:10  |
Pat McCormack
Messages: 2
Registered: November 2000
|
Junior Member |
|
|
If anyone is able to tell me how to check to see if a string value can be converted to a valid date via a sql statement I would appreciate it. For example, in VB or MSAccess SQL I can check a string with an IsDate() function. The data is char and in yyyymmdd format. I've tried inserting into a temp table that has a date field as show below but that fails...I'm stumped.
INSERT INTO TEST_DATES SELECT TEST_EMP.SSN,
SUBSTR(TEST_EMP.BIRTH_DATE, 5,2) || '/' || SUBSTR(TEST_EMP.BIRTH_DATE, 7,2) || '/' || SUBSTR(TEST_EMP.BIRTH_DATE, 1,4) "TESTDATE"
FROM TEST_EMP
WHERE TO_NUMBER(birth_date) > 0
OR birth_date IS NOT NULL;
|
|
|
|
| Re: Date Validation [message #371651 is a reply to message #371646] |
Mon, 20 November 2000 09:30  |
Babu Paul
Messages: 38
Registered: November 2000
|
Member |
|
|
Hi,
In ORACLE the default Date format is DD-Mon-YY, so any date value should be in the format mentioned above. There are two ways of inserting a record with different date format. Either altering the session modifying nls_date_format to whatever date format you want or using the TO_DATE function and forcing the date format.
For ex. TO_DATE('01-01-00','dd-mm-yy') or TO_DATE('01/01/2000','dd/mm/yyyy')
So make use of the TO_DATE function before the substr function, this should create a record successfully.
INSERT INTO TEST_DATES SELECT TEST_EMP.SSN,
TO_DATE(SUBSTR(TEST_EMP.BIRTH_DATE, 5,2) || '/' || SUBSTR(TEST_EMP.BIRTH_DATE, 7,2) || '/' || SUBSTR(TEST_EMP.BIRTH_DATE, 1,4),'MM/DD/YYYY')
FROM TEST_EMP
WHERE TO_NUMBER(birth_date) > 0
OR birth_date IS NOT NULL;
Good Luck!
Babu
|
|
|
|
|
|
| Re: Date Validation [message #372315 is a reply to message #371646] |
Thu, 01 February 2001 15:52 |
KAW
Messages: 8
Registered: February 2001
|
Junior Member |
|
|
This works well for me. It is not pretty and maybe someone can clean it up. It seems to handle
this format also--> ('January 20 1999'). Try it out.
-- Validate DATE datatype
Function IsDate(P_Value IN VARCHAR2)
Return Boolean
IS
V_Date_Value DATE;
IsDate0 BOOLEAN;
IsDate1 BOOLEAN;
IsDate2 BOOLEAN;
IsDate3 BOOLEAN;
IsDate4 BOOLEAN;
BEGIN
BEGIN
SELECT To_Date(P_Value,'mm/dd/yy') INTO V_Date_Value FROM dual;
IsDate0 := TRUE;
EXCEPTION
WHEN OTHERS THEN
IsDate0 := FALSE;
END;
BEGIN
SELECT To_Date(P_Value,'mm/dd/yyyy') INTO V_Date_Value FROM dual;
IsDate1 := TRUE;
EXCEPTION
WHEN OTHERS THEN
IsDate1 := FALSE;
END;
BEGIN
SELECT To_Date(P_Value,'mm-dd-yyyy') INTO V_Date_Value FROM dual;
IsDate2 := TRUE;
EXCEPTION
WHEN OTHERS THEN
IsDate2 := FALSE;
END;
BEGIN
SELECT To_Date(P_Value,'dd-mm-yy') INTO V_Date_Value FROM dual;
IsDate3 := TRUE;
EXCEPTION
WHEN OTHERS THEN
IsDate3 := FALSE;
END;
BEGIN
SELECT To_Date(P_Value,'mm-dd-yy') INTO V_Date_Value FROM dual;
IsDate4 := TRUE;
EXCEPTION
WHEN OTHERS THEN
IsDate4 := FALSE;
END;
IF (IsDate0 = TRUE Or IsDate1 = TRUE Or IsDate2 = TRUE
Or IsDate3 = TRUE Or IsDate4 = TRUE) AND P_Value IS NOT NULL THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
End IsDate; -- End IsDate
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
