Happy New Year

Lenny77 · #1 (**permalink**) 12-28-09, 13:07

!!! Happy New Year to All of You !!!

You can use this DB2 calendar:

Code:

with 
last_day(last_day) as 
(select date('2009-12-31') from sysibm.sysdummy1 )
,
calendar(clnd_day, clnd_dayofweek, clnd_day_no) as
(select last_day, varchar('', 3), int(0) from last_day
union all
select last_day + (clnd_day_no + 1) day, 
substr('SunMonTueWedThuFriSat', 
          3 * (dayofweek(last_day + (clnd_day_no + 1) day) - 1) + 1 , 3),
clnd_day_no + 1
from calendar, last_day
where clnd_day_no  <= 365 
and
year(last_day + (clnd_day_no + 1) day) <= 2010
)
select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
from calendar, last_day
where year(clnd_day) > year(last_day)

Lenny

Lenny77 · #2 (**permalink**) 12-28-09, 14:50

This calendar also works:

Code:

with 
last_day(last_day) as 
(select current date - dayofyear(current date) days + 1 year 
   from sysibm.sysdummy1 )
,
calendar(clnd_day, clnd_dayofweek, clnd_day_no) as
(select last_day, varchar('', 3), int(0) from last_day
union all
select clnd_day + 1 day, 
substr('SunMonTueWedThuFriSat', 
          3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
clnd_day_no + 1
from calendar
where clnd_day_no  <= 366 
)
select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
from calendar, last_day
where year(clnd_day) = year(last_day) + 1

Lenny

Lenny77 · #3 (**permalink**) 12-28-09, 16:34

You can join me with yours New Year wishes and yours calendar queries....

Lenny

Lenny77 · #4 (**permalink**) 12-29-09, 13:27

!!! Holiday !!!

Code:

select 
current date - (dayofyear(current date) - 1)  days + 1 year 
from sysibm.sysdummy1

Lenny

db2girl · #5 (**permalink**) 12-30-09, 11:47

С Наступающим Новым Годом!

Happy New Year!

Lenny77 · #6 (**permalink**) 12-31-09, 10:14

This calendar will change only on the last day of the current year:

Code:

with 
last_day(last_day) as 
(
select 
case 
when month(current date) = 12 and day(current date) = 31 then  
   current date - dayofyear(current date) days + 1 year 
else 
   current date - dayofyear(current date) days 
end
from sysibm.sysdummy1 
)
,
calendar(clnd_day, clnd_dayofweek, clnd_day_no) as
(
select last_day, varchar('', 3), int(0) from last_day
union all
select clnd_day + 1 day, 
substr('SunMonTueWedThuFriSat', 
          3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
clnd_day_no + 1
from calendar
where clnd_day_no  <= 366 
)
select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
from calendar, last_day
where year(clnd_day) = year(last_day) + 1

Lenny

Lenny77 · #7 (**permalink**) 01-04-10, 17:46

Calendar with some Holidays.
You can add your own holidays, using the same logics:

Code:

with 
last_day(last_day) as 
(select 
case 
when month(current date) = 12 and day(current date) = 30 then  
   current date - dayofyear(current date) days + 1 year 
else 
   current date - dayofyear(current date) days 
end
from sysibm.sysdummy1 
)
,
calendar(clnd_day, clnd_dayofweek, clnd_day_no, scheduling) as
(select last_day, varchar('', 3), int(0), varchar('', 100) 
from last_day
union all
select clnd_day + 1 day, 
substr('SunMonTueWedThuFriSat', 
          3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
clnd_day_no + 1,
case 
  when (month(clnd_day + 1 day) = 1  and day(clnd_day + 1 day) = 1  )
  then 'New Year'
  when (month(clnd_day + 1 day) = 12 and day(clnd_day + 1 day) = 25)
  then 'Christmas Day'
  when (month(clnd_day + 1 day) = 7 and day(clnd_day + 1 day) = 4)
  then 'Independence Day'
  when (month(clnd_day + 1 day) = 11 and dayofweek(clnd_day + 1 day) = 5)
        and month(clnd_day + 8 day) = 12 
  then 'Thanksgiving Day'
  when (month(clnd_day + 1 day) = 2 and dayofweek(clnd_day + 1 day) = 2)
        and month(clnd_day - 6 day ) = 2 and month(clnd_day - 13 day) = 2 
        and month(clnd_day - 20 day) = 1  
  then 'President''s Day'
  when dayofweek(clnd_day + 1 day) in (2, 3, 4, 5, 6)
  then 'WeekDay'
  when dayofweek(clnd_day + 1 day) in (1, 7)
  then 'WeekEnd'
end 
from calendar
where clnd_day_no  <= 366 
)
select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
, scheduling
from calendar, last_day
where year(clnd_day) = year(last_day) + 1

Lenny

Lenny77 · #8 (**permalink**) 01-14-10, 10:58

!!! Happy New "Old Russian" Year !!!

Lenny77 · #9 (**permalink**) 01-03-11, 12:18

! Happy New Year 2011 !

The calendar function which I have created 1 year ago still working without any corrections:

Code:

with 
last_day(last_day) as 
(select 
case 
when month(current date) = 12 and day(current date) = 30 then  
   current date - dayofyear(current date) days + 1 year 
else 
   current date - dayofyear(current date) days 
end
from sysibm.sysdummy1 
)
,
calendar(clnd_day, clnd_dayofweek, clnd_day_no, scheduling) as
(select last_day, varchar('', 3), int(0), varchar('', 100) 
from last_day
union all
select clnd_day + 1 day, 
substr('SunMonTueWedThuFriSat', 
          3 * (dayofweek(clnd_day + 1 day) - 1) + 1 , 3),
clnd_day_no + 1,
case 
  when (month(clnd_day + 1 day) = 1  and day(clnd_day + 1 day) = 1  )
  then 'New Year'
  when (month(clnd_day + 1 day) = 12 and day(clnd_day + 1 day) = 25)
  then 'Christmas Day'
  when (month(clnd_day + 1 day) = 7 and day(clnd_day + 1 day) = 4)
  then 'Independence Day'
  when (month(clnd_day + 1 day) = 11 and dayofweek(clnd_day + 1 day) = 5)
        and month(clnd_day + 8 day) = 12 
  then 'Thanksgiving Day'
  when (month(clnd_day + 1 day) = 2 and dayofweek(clnd_day + 1 day) = 2)
        and month(clnd_day - 6 day ) = 2 and month(clnd_day - 13 day) = 2 
        and month(clnd_day - 20 day) = 1  
  then 'President''s Day'
  when dayofweek(clnd_day + 1 day) in (2, 3, 4, 5, 6)
  then 'WeekDay'
  when dayofweek(clnd_day + 1 day) in (1, 7)
  then 'WeekEnd'
end 
from calendar
where clnd_day_no  <= 366 
)
select clnd_day "Day of Year", clnd_dayofweek "Day of Week"
, scheduling
from calendar, last_day
where year(clnd_day) = year(last_day) + 1
;

Internet Services

Web Development

Digital Marketing

Consumer Tech