Solved

handle leap year

Posted on 2008-06-26
4
1,042 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
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 47

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Array of Key/Value Pairs as input to Oracle function 10 53
Schema creation in Oracle12c 6 39
Determine Who is Runnig my Bash Shell Script 4 64
SQL query question 8 40
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

920 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now