Solved

handle leap year

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

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Subquery in Oracle: Sub queries are one of advance queries in oracle. Types of advance queries: •      Sub Queries •      Hierarchical Queries •      Set Operators Sub queries are know as the query called from another query or another subquery. It can …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

18 Experts available now in Live!

Get 1:1 Help Now