Jeff Moden (5/6/2014)
ScottPletcher (5/6/2014)
No reason for the overhead of reading a table just for that. Or to calculate, say, the first Tuesday or last Friday, etc., of a month. Calendar tables just create issues there where none should exist.I tend to agree except for certain esoteric things. Do you have code that you'd like to share for the two "problems" you cited above?
I'll share:
select
MonthDaySeven,
FirstSun = dateadd(dd,(datediff(dd,-53684,a.MonthDaySeven)/7)*7,-53684),
FirstMon = dateadd(dd,(datediff(dd,-53690,a.MonthDaySeven)/7)*7,-53690),
FirstTue = dateadd(dd,(datediff(dd,-53689,a.MonthDaySeven)/7)*7,-53689),
FirstWed = dateadd(dd,(datediff(dd,-53688,a.MonthDaySeven)/7)*7,-53688),
FirstThu = dateadd(dd,(datediff(dd,-53687,a.MonthDaySeven)/7)*7,-53687),
FirstFri = dateadd(dd,(datediff(dd,-53686,a.MonthDaySeven)/7)*7,-53686),
FirstSat = dateadd(dd,(datediff(dd,-53685,a.MonthDaySeven)/7)*7,-53685)
from
(select MonthDaySeven=dateadd(dd,6,dateadd(mm,datediff(mm,0,getdate()),0))) a
select
MonthLastDay,
LastSun = dateadd(dd,(datediff(dd,-53684,a.MonthLastDay)/7)*7,-53684),
LastMon = dateadd(dd,(datediff(dd,-53690,a.MonthLastDay)/7)*7,-53690),
LastTue = dateadd(dd,(datediff(dd,-53689,a.MonthLastDay)/7)*7,-53689),
LastWed = dateadd(dd,(datediff(dd,-53688,a.MonthLastDay)/7)*7,-53688),
LastThu = dateadd(dd,(datediff(dd,-53687,a.MonthLastDay)/7)*7,-53687),
LastFri = dateadd(dd,(datediff(dd,-53686,a.MonthLastDay)/7)*7,-53686),
LastSat = dateadd(dd,(datediff(dd,-53685,a.MonthLastDay)/7)*7,-53685)
from
( select MonthLastDay = dateadd(mm,datediff(mm,-1,getdate()),-1) ) a
Results:
MonthDaySeven FirstSun FirstMon FirstTue FirstWed FirstThu FirstFri FirstSat
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2014-05-07 00:00:00.000 2014-05-04 00:00:00.000 2014-05-05 00:00:00.000 2014-05-06 00:00:00.000 2014-05-07 00:00:00.000 2014-05-01 00:00:00.000 2014-05-02 00:00:00.000 2014-05-03 00:00:00.000
MonthLastDay LastSun LastMon LastTue LastWed LastThu LastFri LastSat
----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2014-05-31 00:00:00.000 2014-05-25 00:00:00.000 2014-05-26 00:00:00.000 2014-05-27 00:00:00.000 2014-05-28 00:00:00.000 2014-05-29 00:00:00.000 2014-05-30 00:00:00.000 2014-05-31 00:00:00.000
Or, you can encapsulate the start of week logic in a function:
Start of Week Function: