Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

handle leap year

Posted on 2008-06-26
4
Medium Priority
?
1,049 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 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 Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

688 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