Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

handle leap year

Posted on 2008-06-26
4
Medium Priority
?
1,050 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 200 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 200 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 200 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 1400 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

783 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