All Forums Database
prakhar 101 posts Joined 05/08
20 Jan 2009
System calendar

REPLACE VIEW CALBASICS( calendar_date, day_of_calendarSEL 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 endFROM CALDATES;Not able to get the logic on which it has been calcluated ......can anybody tell me logic behind it.....

ispaleny 11 posts Joined 12/04
19 Feb 2009

Leat's assume DATE AS YYYY-MM-DD, THENcdate/1 + 19000000 ... INTEGER YYYYMMDDcdate MOD 10000 ... INTEGER MMDD(cdate MOD 10000)/100 ... INTEGER MMcdate/10000 + 1900 ... INTEGER YYYY2009-02-19109 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 100AS day_of_monthSELECT 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_calendarFROM CALDATES;

You must sign in to leave a comment.