Home » SQL & PL/SQL » SQL & PL/SQL » Check valid date (10g)
Check valid date [message #400679] Wed, 29 April 2009 04:39 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Is there any way to check if date is valid in sql itself.

Im actually using substr to extract date out from a field, i need to check if the date extracted is valid before inserting into target table.

Thank You
Re: Check valid date [message #400683 is a reply to message #400679] Wed, 29 April 2009 04:53 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
to_date it and see if you get an error
Re: Check valid date [message #400684 is a reply to message #400679] Wed, 29 April 2009 04:53 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
You don't have to "check" anything because Oracle will not let you insert invalid value into a DATE datatype column (such as 30 February, for example).
Re: Check valid date [message #400706 is a reply to message #400684] Wed, 29 April 2009 05:45 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi,

Actually the issue is that i dont want this record to fail during insertion to target.

Because if its not a valid date, i want to initialize this column to null and insert into the table.

Im just looking if theres any approach to do this within sql, just to check if its a valid date, and if it is not, i will initialize it to null
Re: Check valid date [message #400708 is a reply to message #400706] Wed, 29 April 2009 05:54 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
No way in SQL.

regards,
Delna
Re: Check valid date [message #400709 is a reply to message #400679] Wed, 29 April 2009 05:57 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
In sql you can't. In PL/SQL you can do what I suggested.
Re: Check valid date [message #400711 is a reply to message #400708] Wed, 29 April 2009 06:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Unless you create a user defined function like this one, which I use for a similar case :

SQL> CREATE OR REPLACE FUNCTION save_todate(
  2     INCHAR IN VARCHAR, INFORMAT IN VARCHAR
  3  ) RETURN date AS
  4
  5    v_temp_date date;
  6
  7  BEGIN
  8
  9    v_temp_date := To_date(INCHAR,INFORMAT);
 10
 11    RETURN v_temp_date;
 12
 13  EXCEPTION
 14    WHEN OTHERS THEN
 15      RETURN null;
 16  END;
 17  /

Function created.

SQL>
SQL>
SQL>
SQL> SELECT save_todate('01.01.2009','dd.mm.yyyy') FROM dual;

SAVE_TODA
---------
01-JAN-09

SQL>
SQL> SELECT save_todate('01.45.2009','dd.mm.yyyy') FROM dual;

SAVE_TODA
---------


SQL>
SQL> SELECT save_todate('01.10.2009','dd.mm.yyyy') FROM dual;

SAVE_TODA
---------
01-OCT-09

SQL>
SQL> SELECT save_todate('44.10.2009','dd.mm.yyyy') FROM dual;

SAVE_TODA
---------


SQL>
Re: Check valid date [message #400712 is a reply to message #400711] Wed, 29 April 2009 06:04 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
thank you guys, appreacite it
Re: Check valid date [message #400713 is a reply to message #400708] Wed, 29 April 2009 06:09 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This is Oracle - there's (almost) always a way.
You'd need to create a function like this:
CREATE OR REPLACE FUNCTION valid_date (p_string in varchar2
                                      ,p_format in varchar2 default 'dd/mm/yyyy')
                                      return date is
  v_return  date;
BEGIN
  v_return := to_date(p_string,p_format);
  return v_return;
EXCEPTION
  when others then
    return null;
END;
/


Then you can do something like
CREATE TABLE test_185 (col_1 date);

insert into test_185 values (valid_date('28/02/2009'));
insert into test_185 values (valid_date('29/02/2009'));

select * from test_185;


[Too sloowwww!!!!!]

[Updated on: Wed, 29 April 2009 06:09]

Report message to a moderator

Previous Topic: function to convert alphanumeric character to number
Next Topic: IO cost is high
Goto Forum:
  


Current Time: Fri Dec 09 11:34:12 CST 2016

Total time taken to generate the page: 0.09630 seconds