Home » SQL & PL/SQL » SQL & PL/SQL » FLAG incorrectly set for Feb leap year
FLAG incorrectly set for Feb leap year [message #225753] Wed, 21 March 2007 05:03 Go to next message
satheesh_color
Messages: 1
Registered: March 2007
Junior Member
Hi All,

FLAG was incorrectly set to 'Y' for Feb 29 in 2004 during the report processing of Feb 28 data. Because of this, the extra day of Feb 29 2004 of Sales data was incorrectly being added to a report, thus overstating MTD sales of Feb 28 2004 by one full day. This appears to be caused by the fact that there were 28 days in Feb 2005 and 29 days in Feb 2004 becuase of the leap year. This will not cause problems until the next leap year.

Query:
------
Update day set Flag='Y'
WHERE PERIOD_DATE BETWEEN TRUNC(ADD_MONTHS(TO_DATE(20050227,'YYYYMMDD'),-12),'MON') AND
ADD_MONTHS(TO_DATE(20050227,'YYYYMMDD'),-12)

So it will update the Flag 'Y' to Feb 20040201 through 20040227

Where as if we execute the below query:

Update day set Flag='Y'
WHERE PERIOD_DATE BETWEEN TRUNC(ADD_MONTHS(TO_DATE(20050228,'YYYYMMDD'),-12),'MON') AND
ADD_MONTHS(TO_DATE(20050228,'YYYYMMDD'),-12)

It will update both day flags 20040228 and 20040229.

How can we resolve the issues?

Thanks & Regards,
Satheesh
Re: FLAG incorrectly set for Feb leap year [message #225819 is a reply to message #225753] Wed, 21 March 2007 10:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, your explanation gives me very little idea what the actual result you're aiming for is, but in the interests of getting more details, will this work:
Update day set Flag='Y'
WHERE PERIOD_DATE BETWEEN TRUNC(ADD_MONTHS(TO_DATE(20050228,'YYYYMMDD'),-12),'MON') AND 
ADD_MONTHS(TO_DATE(20050228,'YYYYMMDD'),-12)
AND TO_CHAR(period_date,'DDMM') != '2902'

Re: FLAG incorrectly set for Feb leap year [message #227252 is a reply to message #225753] Tue, 27 March 2007 13:24 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Why have a flag when it is so easy to determine a leap year?

Update day set Flag='Y'
WHERE to_char(last_day(PERIOD_DATE),'MMDD') = '0229';

or to test and repair all the possible flags.

Update day set Flag= DECODE(to_char(last_day(PERIOD_DATE,'MMDD'),'0229','Y','N');

[Updated on: Tue, 27 March 2007 13:28]

Report message to a moderator

Previous Topic: How to load data into two tables from Ms-excel file using script
Next Topic: extracting information from 2 different tables
Goto Forum:
  


Current Time: Tue Dec 06 04:13:09 CST 2016

Total time taken to generate the page: 0.07622 seconds