Home » SQL & PL/SQL » SQL & PL/SQL » validate the date data
validate the date data [message #404519] Fri, 22 May 2009 01:34 Go to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
I wish to know is that any sql command to check the date stored in tables is valid date.
For example, there is data
Due date
------------
29-Feb-2005
30-May-2006

so, is that any way to check that there is an invalid record which is 29-Feb-2005?
Re: validate the date data [message #404520 is a reply to message #404519] Fri, 22 May 2009 01:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the date is stored in a date field, it is valid (unless Oracle bugs which exist).
If it is not, your design is wrong.

That said, you can try to convert it into a date using TO_DATE function and see if it works.

Regards
Michel

[Updated on: Fri, 22 May 2009 02:13]

Report message to a moderator

Re: validate the date data [message #404525 is a reply to message #404519] Fri, 22 May 2009 01:53 Go to previous messageGo to next message
ashoka_bl
Messages: 398
Registered: November 2006
Location: Bangalore
Senior Member

I believe, it will not let you to insert Invalid Date only.

Check,

INSERT INTO EMP ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM,
DEPTNO ) VALUES ( 
1002, 'SMITH', 'CLERK', 7902,  TO_Date( '02/29/2005 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS AM')
, 800, NULL, 20); 


Check the Error Message
Quote:

ORA-01839: date not valid for month specified

Re: validate the date data [message #404532 is a reply to message #404525] Fri, 22 May 2009 02:36 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
But somehow my customer found out that the date stored in the table is corrupted (enter by the system), so it still will stored those kind of invalid date, so is that any command to check out those corrupted date field?
Re: validate the date data [message #404534 is a reply to message #404532] Fri, 22 May 2009 02:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the type of your field?
Post example of "corrupted data".

Regards
Michel
Re: validate the date data [message #404535 is a reply to message #404534] Fri, 22 May 2009 03:02 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
corrupted data such as
99-99-99
I do not know is that any other corrupted data pattern. This is the reason of why I cannot hard code the date '99-99-99' to search out those record with invalid date being stored.
Re: validate the date data [message #404537 is a reply to message #404535] Fri, 22 May 2009 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
If this corrupted data is actually stored in a DATE column, then you'd probably want to raise a SR with Oracle.

If it's stored in a Varchar2 column, then you need to write a function like this:
CREATE OR REPLACE FUNCTION IS_DATE(p_str  in  varchar2
                                  ,p_fmt  in  varchar2) return varchar2 as
  v_date    date;
BEGIN
  v_date := to_date(p_str,p_fmt);
  return 'Y';
EXCEPTION
  WHEN OTHERS THEN
    return 'N'
END;


You can use this to determine the valid dates in a table by:
SELECT date_col,is_date(date_col,'dd-mon-yyyy') FROM table;
, or to clear out invalid dates like this:
UPDATE table 
SET date_col = null
WHERE is_date(date_col,'dd-mm-yyyy') = 'N';
Re: validate the date data [message #404557 is a reply to message #404537] Fri, 22 May 2009 04:52 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
I have tried to use "SELECT date_col,is_date(date_col,'dd-mon-yyyy') FROM table;"

it doesn't work, it will come out with message "invalid column name", but I am sure that the column name that I have entered is correct, doesn't have the "is_date" syntax?
Re: validate the date data [message #404558 is a reply to message #404535] Fri, 22 May 2009 04:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
greentea wrote on Fri, 22 May 2009 10:02
corrupted data such as
99-99-99
I do not know is that any other corrupted data pattern. This is the reason of why I cannot hard code the date '99-99-99' to search out those record with invalid date being stored.

For the Nth times:

WHAT IS THE DATATYPE OF THE FIELD?

Why don't you answer to this very simple question.

Regards
Michel

Re: validate the date data [message #404560 is a reply to message #404557] Fri, 22 May 2009 04:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
greentea wrote on Fri, 22 May 2009 11:52
I have tried to use "SELECT date_col,is_date(date_col,'dd-mon-yyyy') FROM table;"

it doesn't work, it will come out with message "invalid column name", but I am sure that the column name that I have entered is correct, doesn't have the "is_date" syntax?


Of course this does not work if your table is not named "table" and your column "date_col".

WHY DON'T YOU JUST COPY AND PASTE WHAT YOU DID AND WHAT YOU HAVE?

Regards
Michel

Re: validate the date data [message #404567 is a reply to message #404557] Fri, 22 May 2009 05:17 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
greentea wrote on Fri, 22 May 2009 11:52
I have tried to use "SELECT date_col,is_date(date_col,'dd-mon-yyyy') FROM table;"

it doesn't work, it will come out with message "invalid column name", but I am sure that the column name that I have entered is correct, doesn't have the "is_date" syntax?

IS_DATE is a function provided by JRowbottom; you should have read his message much more carefully than you did.
Re: validate the date data [message #404706 is a reply to message #404558] Fri, 22 May 2009 23:59 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
is the Date format field
I have 2 table:
1) Remark1

Remark1 Data type
------- ---------
text1 VARCHAR2
text2 VARCHAR2
text3 VARCHAR2
text4 VARCHAR2
Due_Date DATE

There is some corrupted data being stored in Due_Date field.

Re: validate the date data [message #404710 is a reply to message #404519] Sat, 23 May 2009 00:17 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>There is some corrupted data being stored in Due_Date field.
Please post example of corrupted data "being stored in Due_Date field.".
Re: validate the date data [message #404796 is a reply to message #404710] Sun, 24 May 2009 01:00 Go to previous messageGo to next message
greentea
Messages: 12
Registered: May 2009
Junior Member
corrupted data such as
99-99-99
Re: validate the date data [message #404799 is a reply to message #404796] Sun, 24 May 2009 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you have to fix the application that enters these dates.
It uses a binary format and should not (for a clear safe reason), instead it should use date functions.

Regards
Michel
Re: validate the date data [message #404800 is a reply to message #404796] Sun, 24 May 2009 01:42 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If DUE_DATE column is of a DATE datatype (as you've said in your previous message), then how did you manage to insert value "99-99-99" into it? Which date format is that? "99" may be only a year; day and month are invalid. Or not?
Re: validate the date data [message #404802 is a reply to message #404800] Sun, 24 May 2009 01:46 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Littlefoot wrote on Sun, 24 May 2009 08:42
If DUE_DATE column is of a DATE datatype (as you've said in your previous message), then how did you manage to insert value "99-99-99" into it? Which date format is that? "99" may be only a year; day and month are invalid. Or not?

This is possible using OCI (maybe also JDBC I didn't check it) and giving date in binary format, this API allows it but it assumes the user knows what it does and gives a correct date and does not check it. I strongly recommend to NOT use this interface and let it for Oracle only.

Regards
Michel

Previous Topic: Count records by week
Next Topic: error access to database
Goto Forum:
  


Current Time: Thu Dec 08 02:19:42 CST 2016

Total time taken to generate the page: 0.10453 seconds