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 jdate = USING("####",cYear)+USING("0###",cDayOfYear), which is why I didn't add it.
Enjoy,
Bill
Here is a little query to run against the Calendar table. ldom = Last Day of Month, fdom = First Day of Month.
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
Added some US Holiday Names.
Bill