Home » SQL & PL/SQL » SQL & PL/SQL » Date Validation
Date Validation [message #221413] Mon, 26 February 2007 10:50 Go to next message
hariyadav
Messages: 12
Registered: February 2007
Location: hyderabad
Junior Member
Hi all,

Good morning.

I have a problem while get the day name from a given date.

For my function,I am sending the date as parameter as FEB 28, 2003, it reuturns as FRI which is correct.

For the next case I am sending FEB 29, 2003 as a parameter it is not valid date.

Before send above date, I need to validate date. whether that date is exist or not.

Can any one please help me how validate whether the given is valid date or not (FEB 29, 2003).

Thanks in advance.

Hari Yadav.G

Report message to a moderator

Re: Date Validation [message #221422 is a reply to message #221413] Mon, 26 February 2007 13:04 Go to previous messageGo to next message
vamshi
Messages: 6
Registered: November 2000
Location: Hyderabad
Junior Member
Hello Friend,

Just check out this function and let me know.

CREATE OR REPLACE FUNCTION chk_year
RETURN BOOLEAN
IS
v_year number;
BEGIN
SELECT TO_CHAR (SYSDATE, 'YYYY')
INTO v_year
FROM DUAL;

IF MOD (v_year, 4) = 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END chk_year;

Report message to a moderator

Re: Date Validation [message #221446 is a reply to message #221413] Mon, 26 February 2007 16:47 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member


CREATE OR REPLACE FUNCTION chk_date(date_str,date_format)
RETURN BOOLEAN
IS
v_year number;
BEGIN
SELECT TO_CHAR (date_str,date_format)
INTO v_year
FROM DUAL;
RETURN TRUE;
exception when others then
RETURN FALSE;
END chk_date;

Report message to a moderator

Re: Date Validation [message #221560 is a reply to message #221422] Tue, 27 February 2007 07:58 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
vamshi wrote on Mon, 26 February 2007 14:04
Hello Friend,

Just check out this function and let me know.

CREATE OR REPLACE FUNCTION chk_year
RETURN BOOLEAN
IS
v_year number;
BEGIN
SELECT TO_CHAR (SYSDATE, 'YYYY')
INTO v_year
FROM DUAL;

IF MOD (v_year, 4) = 0 THEN
RETURN TRUE;
ELSE
RETURN FALSE;
END IF;
END chk_year;


I am wondering how this answer has anything to do with the question.

Report message to a moderator

Re: Date Validation [message #221564 is a reply to message #221560] Tue, 27 February 2007 08:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, it's an answer to the special case that the OP posted, of a Leap year date in a non leap year. @vamshi has decided that this must be the only possible problem that the OP could be having, and solved that, rather than actually answer the OPs question.

Takes all sorts, I guess.

Report message to a moderator

icon3.gif  Re: Date Validation [message #222028 is a reply to message #221413] Thu, 01 March 2007 08:40 Go to previous messageGo to next message
allfair
Messages: 2
Registered: March 2007
Junior Member
This one validate your date checks.

CREATE OR REPLACE FUNCTION chk_date(date_str VARCHAR2, DATE_FORMAT VARCHAR2)
RETURN BOOLEAN IS
v_dummy VARCHAR2(30);
BEGIN
SELECT TO_DATE(date_str, DATE_FORMAT)
INTO v_dummy
FROM DUAL;

RETURN TRUE;
EXCEPTION
WHEN OTHERS THEN
RETURN FALSE;
END chk_date;

Now test it.

SELECT CASE WHEN CHK_DATE('29.02.2003', 'DD.MM.YYYY') THEN 1 ELSE 2 END FROM DUAL;

Output : 2

Report message to a moderator

Re: Date Validation [message #222036 is a reply to message #222028] Thu, 01 March 2007 09:03 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, if you want something that works MUCH quicker, and is usable in SQL without resorting to CASE statements and allows you to use your default format, try this:
CREATE OR REPLACE FUNCTION valid_date(date_str    VARCHAR2
                                     ,date_format VARCHAR2 default null)
RETURN varchar2 IS
  v_date date;
BEGIN
  if date_format is not null then
    v_date := to_date(date_str,date_format);
  else
    v_date := to_date(date_str);
  end if;

  return 'Y';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'N';
END valid_date;


You can use this either in the SELECT clause or the WHERE clause

Report message to a moderator

Previous Topic: Is %ROWCOUNT compatible with 10g?
Next Topic: Loop Through a String
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Dec 05 00:31:30 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.