Home » SQL & PL/SQL » SQL & PL/SQL » Leap years with a twist
Leap years with a twist [message #245951] Tue, 19 June 2007 07:14 Go to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
HI ,

Ok so we know that to see if a year is a leap year we can use something like the following :-

select year,
decode( mod(year, 4), 0,
decode( mod(year, 400), 0, 366,
decode( mod(year, 100), 0, 365, 366)
), 365
) as days_in_year
from (select 2006 year from dual
union all
select 2000 year from dual
union all
select 2004 year from dual
union all
select 1900 year from dual
)
Result
YEAR DAYS_IN_YEAR
2006 365
2000 366
2004 366
1900 365

I need to be able to understand if a leap year date exists between to dates and i would like to do it in sql. There is a horrible hardcoded appropach that I could use but is there a more elegant solution that would not need maitenance ?

WHERE ('29-FEB-1996' BETWEEN START_DT AND END_DT
OR '29-FEB-2000' BETWEEN START_DT AND END_DT
OR '29-FEB-2004' BETWEEN START_DT AND END_DT
OR '29-FEB-2008' BETWEEN START_DT AND END_DT)

So my data would look like this :-

START_DT END_DT
01/01/2000 31/12/2000
01/01/2004 31/12/2004
01/03/2004 28/02/2005

Results I would like are :-

START_DT END_DT LEAP_YR
01/01/2000 31/12/2000 Y
01/01/2004 31/12/2004 Y
01/03/2004 28/02/2005 N

Thanks for your help in advance

P
Re: Leap years with a twist [message #245966 is a reply to message #245951] Tue, 19 June 2007 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> with data as (
  2    select 2006 year from dual
  3    union all
  4    select 2000 year from dual
  5    union all
  6    select 2004 year from dual
  7    union all
  8    select 1900 year from dual
  9    )
 10  select year,
 11         decode(to_date('01/03/'||year,'DD/MM/YYYY')
 12                -to_date('28/02/'||year,'DD/MM/YYYY'),
 13                1, 'Normal', 'Leap') "Type"
 14  from data
 15  /
      YEAR Type
---------- ------
      2006 Normal
      2000 Leap
      2004 Leap
      1900 Normal

4 rows selected.

Regards
Michel

[Updated on: Tue, 19 June 2007 08:08]

Report message to a moderator

Re: Leap years with a twist [message #245968 is a reply to message #245951] Tue, 19 June 2007 07:43 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Pastonp wrote on Tue, 19 June 2007 08:14

WHERE ('29-FEB-1996' BETWEEN START_DT AND END_DT
OR '29-FEB-2000' BETWEEN START_DT AND END_DT
OR '29-FEB-2004' BETWEEN START_DT AND END_DT
OR '29-FEB-2008' BETWEEN START_DT AND END_DT)



Only DATEs can fall between other dates. Strings can never fall between dates. That's like asking how many oranges are colder than apples.

Watch...
FOO SCOTT>l
  1   select 1 from dual where
  2   (  '29-FEB-1996' BETWEEN SYSDATE AND SYSDATE+365
  3   OR '29-FEB-2000' BETWEEN SYSDATE AND SYSDATE+365
  4   OR '29-FEB-2004' BETWEEN SYSDATE AND SYSDATE+365
  5*  OR '29-FEB-2008' BETWEEN SYSDATE AND SYSDATE+365)
  6  /
 (  '29-FEB-1996' BETWEEN SYSDATE AND SYSDATE+365
    *
ERROR at line 2:
ORA-01843: not a valid month

[Updated on: Tue, 19 June 2007 07:47]

Report message to a moderator

Re: Leap years with a twist [message #245970 is a reply to message #245968] Tue, 19 June 2007 07:46 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Quote:
That's like asking how many oranges are colder than apples.

8 (I tested this last week Very Happy
Re: Leap years with a twist [message #245972 is a reply to message #245970] Tue, 19 June 2007 07:48 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
And how many oranges are colder than apples? While you're at it, how many licks does it take to get to the center of a tootsie roll pop?
Re: Leap years with a twist [message #245975 is a reply to message #245972] Tue, 19 June 2007 08:16 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Laughing
Re: Leap years with a twist [message #245979 is a reply to message #245972] Tue, 19 June 2007 08:34 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
And how many oranges are colder than apples?


Uhhhhhhhhh..... That question can be answered without a problem. Both oranges and apples have a temparature, so you can compare them in that regard. Wink
Re: Leap years with a twist [message #245984 is a reply to message #245979] Tue, 19 June 2007 08:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
To answer the original question, a function could be used for that :

CREATE OR REPLACE FUNCTION is_leap (YEAR number) RETURN NUMBER IS
t_date date;
BEGIN
  t_date := To_Date('29.02.' || Trim( To_Char(YEAR,'0000')),'dd.mm.yyyy');
  RETURN 1;

  EXCEPTION
      WHEN OTHERS THEN 
      RETURN 0;
END;
/


Example usage in a Select :

SELECT 1980 + ROWNUM          YEAR, 
       is_leap(1980 + ROWNUM) IS_LEAP
FROM all_objects
Re: Leap years with a twist [message #245993 is a reply to message #245975] Tue, 19 June 2007 09:11 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Hmm...Whats so funny here ?

Oracle clearly understands that '29-FEB-1996' is a date and not a string on my system Razz

All i am asking is if it is possible to replace the where with something less hardcoded...

A simple yes or no would be fine...



SELECT INCPT_R,
EXPRY_R,
366 AS DAYS
FROM my_data
WHERE ('29-FEB-1996' BETWEEN INCPT_R AND EXPRY_R
OR '29-FEB-2000' BETWEEN INCPT_R AND EXPRY_R
OR '29-FEB-2004' BETWEEN INCPT_R AND EXPRY_R
OR '29-FEB-2008' BETWEEN INCPT_R AND EXPRY_R )

Returns :-

INCPT_R EXPRY_R DAYS
------------------------- ------------------------- ----------------------
16-MAR-03 15-MAR-04 366
28-MAR-99 27-MAR-00 366
06-MAR-99 05-MAR-00 366
02-MAR-99 01-MAR-00 366
13-MAR-99 12-MAR-00 366
01-MAR-99 29-FEB-00 366
06-MAR-03 05-MAR-04 366
01-APR-99 31-MAR-00 366
01-APR-99 31-MAR-00 366
16-OCT-99 15-OCT-00 366
02-OCT-99 01-OCT-00 366
02-OCT-99 01-OCT-00 366
02-OCT-99 01-OCT-00 366
02-OCT-99 01-OCT-00 366
02-OCT-99 01-OCT-00 366
02-OCT-99 01-OCT-00 366
31-OCT-99 30-OCT-00 366
01-OCT-99 30-SEP-00 366
Re: Leap years with a twist [message #246006 is a reply to message #245993] Tue, 19 June 2007 09:58 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Pastonp wrote on Tue, 19 June 2007 10:11
Hmm...Whats so funny here ?

Oracle clearly understands that '29-FEB-1996' is a date and not a string on my system Razz



That may be true, but you are falling into a very bad habit and being stubborn. Yes, it works for you, but all you need is for it not to work for just one person, as I clearly showed you it does not work for me, and you're code is not portable.
It may work on your client, but have someone try it using SQL Developer, SQL*Plus on the server, TOAD or something else, or wait for your DBA to change the NLS_DATE_FORMAT of the database and it will fail. Fine, continue to use poor programming techniques and one day (pretty soon) you'll regret it.

[Updated on: Tue, 19 June 2007 10:02]

Report message to a moderator

Re: Leap years with a twist [message #246016 is a reply to message #246006] Tue, 19 June 2007 10:20 Go to previous messageGo to next message
Pastonp
Messages: 13
Registered: November 2006
Junior Member
Yep You are of course 100% right this is really bad practice and I will amend the code in production by wrapping the date string in a conversion funtion with the appropriate format model.

Cheers

P

Re: Leap years with a twist [message #246039 is a reply to message #246016] Tue, 19 June 2007 11:08 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
I am just trying to help. Really, one day it will come back and bite you really bad. Possibly hundreds and hundreds or procedures and functions, scripts, etc. will need to be attended to.
Y2K was a blessing to give everyone a kick in the butt, but it seems to have had only a short-term effect.
Do it right early on and you'll get into a good habit.
Previous Topic: Help me with this Query.
Next Topic: Foreign key on updating
Goto Forum:
  


Current Time: Sat Dec 10 14:46:13 CST 2016

Total time taken to generate the page: 0.09920 seconds