December 01, 2022, 01:21:04 PM

News:

Own IWBasic 2.x ? -----> Get your free upgrade to 3.x now.........


Calendar Table

Started by billhsln, August 23, 2022, 05:18:04 PM

Previous topic - Next topic

0 Members and 1 Guest are viewing this topic.

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  jdate = USING("####",cYear)+USING("0###",cDayOfYear), which is why I didn't add it.

Enjoy,
Bill
When all else fails, get a bigger hammer.

billhsln

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
When all else fails, get a bigger hammer.

billhsln

Added some US Holiday Names.

Bill
When all else fails, get a bigger hammer.