Michael Valentine Jones (5/6/2014)
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:
selectMonthDaySeven,
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:
I love it! We even have a short one for ISO-Week for those still dealing with SQL Server 2005 or less.
http://www.sqlservercentral.com/articles/T-SQL/97910/
BTW, the negative numbers that Michael is using are "integer date serial numbers" for dates from the beginning of the DATETIME calendar. Just like "0" can be used for and is sometime faster than using a literal of '1900' or '1900-01-01', so it is with the negative numbers he used. The "integer serial number" for '1753-01-01' can be found in many different ways. Here's one...
SELECT CAST(DATEADD(dd,0,'1753-01-01') AS INT)
... and that produces the "integer date serial number" of -53690.
Thanks again, Michael.
--Jeff Moden
Change is inevitable... Change for the better is not.