Solved

handle leap year

Posted on 2008-06-26
4
1,047 Views
Last Modified: 2012-06-27
The following doesn't consider leap year date.  
mymonth IN (2) AND (myday < 1 OR myday > 29)

I like to use something like the following.  What is the best way?
 ( mymonth IN (2) AND (myday < 1 OR myday > 29) AND year in 2007, 2008, 2009, 2011,   )
OR
 ( mymonth IN (2) AND (myday < 1 OR myday > 28) AND year in 2010, 2014, 2018  )


0
Comment
Question by:ewang1205
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 50 total points
ID: 21874744
What are you trying to do, just identify invalid dates?  Oracle can do that for you automatically and it will handle leap years automatically.

Here is an example:

create or replace procedure check_date (in_month in number, in_day in number, in_year in number) as
  good_date date;
begin
  good_date := to_date(to_char(in_month) || '/' || to_char(in_day) || '/' || to_char(in_year),'MM/DD/YYYY');
exception
  when others then
    raise_application_error(-20001,'Invalid date: '|| in_month || '/' || in_day || '/' || in_year);
end;
/
0
 
LVL 27

Assisted Solution

by:kretzschmar
kretzschmar earned 50 total points
ID: 21874943
just a bit too late and just the same

CREATE OR REPLACE  
FUNCTION validate_date(pi_day IN NUMBER, pi_month IN NUMBER, pi_year IN NUMBER)
RETURN BOOLEAN
IS
  v_date DATE;
BEGIN
  v_date := TO_DATE(TO_CHAR(pi_day,'FM00')||TO_CHAR(pi_month,'FM00')||TO_CHAR(pi_year,'FM0000'),'ddmmyyyy');
  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RETURN FALSE;
END;    

---

test Script

DECLARE
  d NUMBER := 1;
  m NUMBER := 2;
  y NUMBER := 2000;  
BEGIN
  IF validate_date(d,m,y) THEN
    DBMS_OUTPUT.PUT_LINE('Date is valid');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Date is invalid');
  END IF;
END;  
---

nevertheless

markgeer was first

meikl ;-)

0
 
LVL 22

Assisted Solution

by:DrSQL
DrSQL earned 50 total points
ID: 21875255
ewang1205,
   You can also try doing in without a function:

where -- First, check year is no more than 1 year ago and no more than 10 years from today
     year between to_number(to_char(sysdate,'YYYY')) - 1 and to_number(to_char(sysdate,'YYYY')) +10 and
-- Now check for a valid month
     mymonth between 1 and 12 and
-- And finally, check the day
    myday between 1 and
              to_number(to_char(last_day(to_date('01/' || to_char(mymonth) || '/' || to_char(year),'DD/MM/YYYY')),'DD'))


The last part uses the last_day function to see if the myday is less than or equal to the last day of the month (and takes the DD portion) by creating a date that is sure to have a valid DD portion.

Good luck!

0
 
LVL 48

Accepted Solution

by:
schwertner earned 350 total points
ID: 21875359
( mymonth IN (2) AND (myday >= 1 AND (myday <=29 AND MOD(year - 1960,4) = 0 OR myday <=28 AND MOD(year - 1960,4) <> 0 ) )


MOD(year - 1960,4) returns the remainder of first argument divided by 4

I choose 1960 because J.F.Kennedy was elected as President this year.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

628 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question