Home » SQL & PL/SQL » SQL & PL/SQL » How to check a Valid Date ?
How to check a Valid Date ? [message #45140] Mon, 16 February 2004 02:16 Go to next message
Nirav Parekh
Messages: 27
Registered: March 2001
Junior Member
Dear Friends,
Is there any function in Oracle , which tells me whether the date is valid or not ?
Means suppose i have value of '02/23/2004 12:12:12'.
And suppse second value is '02/23/2004 77:77:12'

Now Except "To_Date", can i know whether this date is valid or not ?

Because if i Use "To_Date" then it will give me error if date is not valid in my select statement.

Pls Help me for this
Nirav Parekh
Re: How to check a Valid Date ? [message #45141 is a reply to message #45140] Mon, 16 February 2004 02:37 Go to previous messageGo to next message
Keshav
Messages: 17
Registered: December 2003
Junior Member
Yes,
you can use the error as check for the the genuine date.
e.g.

begin
select to_Date(date_string,date_format) from dual
exception
when invalid_date then
dbms_output.put_line('Enter appropriate date');
end;
/

Regards,
Keshav
Re: How to check a Valid Date ? [message #45145 is a reply to message #45141] Mon, 16 February 2004 19:36 Go to previous messageGo to next message
Ajendra Naraya Samal
Messages: 26
Registered: December 2003
Junior Member
Is it possible to store '02/23/2004 77:77:12'
in a date field ? If its a string coulmn then we need to convert into date. If its a date col., then it can only store a valid date. Am I wrong anywhere ?
Re: How to check a Valid Date ? [message #45146 is a reply to message #45145] Mon, 16 February 2004 19:48 Go to previous messageGo to next message
Keshav
Messages: 17
Registered: December 2003
Junior Member
It's not possible to store the above string into the date field becoz the hour should lie between 0-23(or 1-12). if it is a string column then it's ok but for the date column it must obey certain rules.

To force these rules, u can use exception handlers.

Regards,
Keshav
Re: How to check a Valid Date ? [message #45155 is a reply to message #45140] Tue, 17 February 2004 11:29 Go to previous messageGo to next message
Nanduri V Rao
Messages: 13
Registered: January 2000
Junior Member
Assign the date value into a date variable. If the format is not correct you will get an error, catch that exception and handle it appropriately.

This way you can avoid the sql query from failing.
Re: How to check a Valid Date ? [message #45168 is a reply to message #45146] Wed, 18 February 2004 00:49 Go to previous message
Ajendra Narayan Samal
Messages: 4
Registered: January 2004
Junior Member
Thanks Keshav, i too got my doubt cleared. Parekh must have been satisfied with yr answer
Previous Topic: Blob for insert .doc/.pdf file into oracle column
Next Topic: Pulling in Quantity on SELECT DISTINCT with MAX Date
Goto Forum:
  


Current Time: Thu May 29 01:04:30 CDT 2025