System calendar

Database
Highlighted
Enthusiast

System calendar

REPLACE VIEW CALBASICS(
calendar_date,
day_of_calendar
SEL
cdate,
case
when (((cdate mod 10000) / 100) > 2) then
(146097 * ((cdate/10000 + 1900) / 100)) / 4
+(1461 * ((cdate/10000 + 1900) - ((cdate/10000 + 1900) / 100)*100) ) / 4
+(153 * (((cdate mod 10000)/100) - 3) + 2) / 5
+ cdate mod 100 - 693901
else
(146097 * (((cdate/10000 + 1900) - 1) / 100)) / 4
+(1461 * (((cdate/10000 + 1900) - 1) - (((cdate/10000 + 1900) - 1) / 100)*100) ) / 4
+(153 * (((cdate mod 10000)/100) + 9) + 2) / 5
+ cdate mod 100 - 693901
end
FROM CALDATES;

Not able to get the logic on which it has been calcluated ......can anybody tell me logic behind it.....
1 REPLY
Enthusiast

Re: System calendar

Leat's assume DATE AS YYYY-MM-DD, THEN

cdate/1 + 19000000 ... INTEGER YYYYMMDD
cdate MOD 10000 ... INTEGER MMDD
(cdate MOD 10000)/100 ... INTEGER MM
cdate/10000 + 1900 ... INTEGER YYYY

2009-02-19
109 02 19
1 00 00
1 00


SELECT
cdate
AS calendar_date

,
CASE
WHEN ((cdate MOD 10000) / 100) > 2 /* MM > 2 => after February */
THEN
/* Use current year February length */
(146097 * ((cdate/10000 + 1900) / 100)) / 4
+(1461 * ((cdate/10000 + 1900) - ((cdate/10000 + 1900) / 100)*100) ) / 4
+(153 * (((cdate MOD 10000)/100) - 3) + 2) / 5
+ cdate MOD 100 - 693901
ELSE
/* Do not use current year February length */
(146097 * (((cdate/10000 + 1900) - 1) / 100)) / 4
+(1461 * (((cdate/10000 + 1900) - 1) - (((cdate/10000 + 1900) - 1) / 100)*100) ) / 4
+(153 * (((cdate MOD 10000)/100) + 9) + 2) / 5
+ cdate MOD 100 - 693901
END
AS day_of_calendar /* sequential day number ... 1900-01-01 is 1 */

,
cdate MOD 100
AS day_of_month

SELECT 59-31

,
(CASE (cdate MOD 10000)/100
/* Compensation numbers for 12 months; 28 days in February */
WHEN 1 THEN cdate MOD 100
WHEN 2 THEN cdate MOD 100 + 31
WHEN 3 THEN cdate MOD 100 + 59
WHEN 4 THEN cdate MOD 100 + 90
WHEN 5 THEN cdate MOD 100 + 120
WHEN 6 THEN cdate MOD 100 + 151
WHEN 7 THEN cdate MOD 100 + 181
WHEN 8 THEN cdate MOD 100 + 212
WHEN 9 THEN cdate MOD 100 + 243
WHEN 10 THEN cdate MOD 100 + 273
WHEN 11 THEN cdate MOD 100 + 304
WHEN 12 THEN cdate MOD 100 + 334
END)
+
(CASE
WHEN (
/* leap year
http://en.wikipedia.org/wiki/Leap_year
it is used every 400 years
and every 4 years (except every 100 years)
starting 1900
*/
(
((cdate / 10000 + 1900) MOD 4 = 0)
AND
((cdate / 10000 + 1900) MOD 100 <> 0)
)
OR
((cdate / 10000 + 1900) MOD 400 = 0)
)
AND ((cdate MOD 10000)/100 > 2)
THEN
1 /* +1 (29) days in February */
ELSE
0 /* +0 (28) days in February */
END)
AS day_of_year

,
(cdate MOD 10000)/100
AS month_of_year

,
cdate/10000
AS year_of_calendar

FROM CALDATES;