Calendar Table

[billhsln]

I was looking around on Reddit's SQL site (?) and came across the concept of a Calendar Table.  So, I decided to create one using IWB and M$ Access.  I added what I thought would be good fields that might be needed, but there might be other fields that you can think of that might be good to have. Please let me know what you think might be good to add.  Also, maybe some one could create versions for other DB SQL versions, like sqlite (I am not very good at sqlite).

Just in case someone asks about doing Julian dates, it is easy 

Code Select Expand

jdate = USING("####",cYear)+USING("0###",cDayOfYear), which is why I didn't add it.

Enjoy,
Bill

[billhsln]

Here is a little query to run against the Calendar table.  ldom = Last Day of Month, fdom = First Day of Month.

Code Select Expand

select cdate
      ,dateadd('m',datediff('m',0,cdate)-1,1) as prev_ldom
      ,dateadd('m',datediff('m',0,cdate)-1,2) as fdom
      ,dateadd('m',datediff('m',-1,cdate),1) as ldom
      ,dateadd('d',-1,dateserial(year(dateadd('m',1,cDate)),month(dateadd('m',1,cDate)),'1')) as curr_ldom
      ,dateadd('m',datediff('m',1,cdate),2) as next_fdom
      ,dateadd('d',0,dateserial(year(dateadd('m',1,cDate)),month(dateadd('m',1,cDate)),'1')) as next_fdom2
      ,dateadd('d',-1,dateserial(year(dateadd('m',1,cDate)),month(dateadd('m',2,cDate)),'1')) as next_ldom
      ,dateadd('d',0,dateserial(year(dateadd('m',1,cDate)),month(dateadd('m',2,cDate)),'1')) AS next2_fdom
      ,dateadd('d',-1,dateserial(year(dateadd('m',1,cDate)),month(dateadd('m',3,cDate)),'1')) AS next2_ldom
  from calendar
 where cYear = 2000
   and cDay = 1;
Bill

[billhsln]

Added some US Holiday Names.

Bill