# Fun in DateTime Calculations

,

Recently I was working on a task that needs to handle various datetime calculations, and it is really fun.

Usually there are two types of approaches, one is to convert datetime value to string value and then through string calculations like substring, concatenation and convertion to get the result, another is via pure datetime functions, like dateadd and datediff. I like the second approach, which is better in performance.

I learned a lot from Itzik Ben-Gan’s series articles here: (DateTime Calculations), especially the Language-Independent (i.e. @DateFirst non-relevant) weekday calculation in part 2 of the series.

`   1: -- 1 find the first day of the prev month / current month / next month`

`   2: declare @dt datetime  = getdate();`

`   3: select prev_mth_1st_day = dateadd(month, datediff(month, 0, @dt)-1, 0)`

`   4: , curr_mth_1st_day=dateadd(month, datediff(month, 0, @dt), 0)`

`   5: , next_mth_1st_day=dateadd(month, datediff(month, 0, @dt)+1, 0)`

`   6:`

`   7: -- 2 find the last day of the prev month / current month / next month`

`   8: select prev_mth_last_day = dateadd(month, datediff(month, 0, @dt),  -1)`

`   9: , curr_mth_1ast_day=dateadd(month, datediff(month, 0, @dt)+1, -1)`

`  10: , next_mth_1ast_day=dateadd(month, datediff(month, 0, @dt)+2, -1)`

`  11:`

`  12: -- 3.1 find the first week day of the prev month / current month / next month`

`  13: select prev_mth_1st_wkday=case datepart(dw, dateadd(month, datediff(month, 0, @dt)-1,0)+@@datefirst-1)`

`  14:         when 6 then  dateadd(month, datediff(month, 0, @dt)-1,0)+2`

`  15:         when 7 then  dateadd(month, datediff(month, 0, @dt)-1,0)+1`

`  16:         else   dateadd(month, datediff(month, 0, @dt)-1,0) end`

`  17: , curr_mth_1st_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1)`

`  18:         when 6 then  dateadd(month, datediff(month, 0, @dt),0)+2`

`  19:         when 7 then  dateadd(month, datediff(month, 0, @dt),0)+1`

`  20:         else   dateadd(month, datediff(month, 0, @dt),0) end`

`  21: , next_mth_1st_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1)`

`  22:         when 6 then  dateadd(month, datediff(month, 0, @dt)+1,0)+2`

`  23:         when 7 then  dateadd(month, datediff(month, 0, @dt)+1,0)+1`

`  24:         else   dateadd(month, datediff(month, 0, @dt)+1,0) end;`

`  25:`

`  26: -- 3.2 find the first weekend day of the prev month / current month / next month`

`  27: select prev_mth_1st_wkndday=case when datepart(dw, dateadd(month, datediff(month, 0, @dt)-1,0)+@@datefirst-1) < 6`

`  28:         then dateadd(month, datediff(month, 0, @dt)-1,0)+ 6- datepart(dw, dateadd(month, datediff(month, 0, @dt)-1,0)+@@datefirst-1)`

`  29:         else   dateadd(month, datediff(month, 0, @dt)-1,0) end`

`  30: , curr_mth_1st_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1) < 6`

`  31:         then dateadd(month, datediff(month, 0, @dt),0)+ 6- datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1)`

`  32:         else   dateadd(month, datediff(month, 0, @dt),0) end`

`  33: , next_mth_1st_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1) < 6`

`  34:         then dateadd(month, datediff(month, 0, @dt)+1,0)+ 6- datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1)`

`  35:         else   dateadd(month, datediff(month, 0, @dt)+1,0) end;`

`  36:`

`  37: -- 4.1 find the last week day of the prev month / current month / next month       `

`  38: select prev_mth_last_wkday=case datepart(dw, dateadd(month, datediff(month, 0, @dt),0)-1+@@datefirst-1)`

`  39:         when 6 then  dateadd(month, datediff(month, 0, @dt),0)-1-1`

`  40:         when 7 then  dateadd(month, datediff(month, 0, @dt),0)-1-2`

`  41:         else   dateadd(month, datediff(month, 0, @dt),0)-1 end`

`  42: , curr_mth_last_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)-1+@@datefirst-1)`

`  43:         when 6 then  dateadd(month, datediff(month, 0, @dt)+1,0)-1-1`

`  44:         when 7 then  dateadd(month, datediff(month, 0, @dt)+1,0)-1-2`

`  45:         else   dateadd(month, datediff(month, 0, @dt)+1,0)-1 end`

`  46: , next_mth_last_wkday = case datepart(dw, dateadd(month, datediff(month, 0, @dt)+2,0)-1+@@datefirst-1)`

`  47:         when 6 then  dateadd(month, datediff(month, 0, @dt)+2,0)-1-1`

`  48:         when 7 then  dateadd(month, datediff(month, 0, @dt)+2,0)-1-2`

`  49:         else   dateadd(month, datediff(month, 0, @dt)+2,0)-1 end;`

`  50:`

`  51: -- 4.2 find the last weekend day of the prev month / current month / next month       `

`  52: select prev_mth_last_wkndday= case when  datepart(dw, dateadd(month, datediff(month, 0, @dt),0)-1+@@datefirst-1) < 6`

`  53:         then dateadd(month, datediff(month, 0, @dt),0)-1-datepart(dw, dateadd(month, datediff(month, 0, @dt),0)-1+@@datefirst-1)`

`  54:         else   dateadd(month, datediff(month, 0, @dt),0)-1 end`

`  55: , curr_mth_last_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)-1+@@datefirst-1) < 6`

`  56:         then dateadd(month, datediff(month, 0, @dt)+1,0)-1-datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)-1+@@datefirst-1)`

`  57:         else   dateadd(month, datediff(month, 0, @dt)+1,0)-1 end`

`  58: , next_mth_last_wkndday = case when  datepart(dw, dateadd(month, datediff(month, 0, @dt)+2,0)-1+@@datefirst-1) < 6`

`  59:         then dateadd(month, datediff(month, 0, @dt)+2,0)-1-datepart(dw, dateadd(month, datediff(month, 0, @dt)+2,0)-1+@@datefirst-1)`

`  60:         else   dateadd(month, datediff(month, 0, @dt)+2,0)-1 end;`

`  61:`

`  62:  go`

`  63:`

`  64: -- 5 find the first nth weekday (Mon/Tue/Wed/Thu/Fri/Sat/Sun) of the month where @dt is in`

`  65: -- example, find the 5th Friday of the month of Aug, 2013 `

`  66: declare @nth int=5, @dt datetime='2013-08-12';`

`  67: declare @dw tinyint  =5 -- 1=Mon,2= Tue,3=Wed, 4=Thur, 5=Fri, 6=Sat, 7=Sun`

`  68:`

`  69: select [1st_nth_wkday]=case when  datepart(dw, dateadd(month, datediff(month,0,@dt),0)+@@datefirst-1) >= @dw`

`  70: then dateadd(day, (@nth-1)*7 + (7-(datepart(dw, dateadd(month, datediff(month,0,@dt),0)+@@datefirst-1)-@dw)), dateadd(month, datediff(month,0,@dt),0))`

`  71: else dateadd(day, (@nth-1)*7 + (0-(datepart(dw, dateadd(month, datediff(month,0,@dt),0)+@@datefirst-1)-@dw)), dateadd(month, datediff(month,0,@dt),0))`

`  72: end`

`  73: go`

`  74:`

`  75:`

`  76: -- 6 find the last nth weekday (Mon/Tue/Wed/Thu/Fri/Sat/Sun) of the month where @dt is in`

`  77:`

`  78: -- find the 2nd last Sunday of current month`

`  79: declare @nth int=2, @dt datetime=current_timestamp;`

`  80: declare @dw tinyint  =7 -- 1=Mon,2= Tue,3=Wed, 4=Thur, 5=Fri, 6=Sat, 7=Sun`

`  81:`

`  82: select [last_nth_wkday]=case when datepart(dw, dateadd(month, datediff(month,0,@dt)+1,0)-1+@@datefirst-1) >= @dw`

`  83: then dateadd(day, -(@nth-1)*7 - (datepart(dw, dateadd(month, datediff(month,0,@dt)+1,0)-1+@@datefirst-1)-@dw), dateadd(month, datediff(month,0,@dt)+1,0)-1)`

`  84: else dateadd(day, -(@nth)*7 - (datepart(dw, dateadd(month, datediff(month,0,@dt)+1,0)-1+@@datefirst-1)-@dw), dateadd(month, datediff(month,0,@dt)+1,0)-1)`

`  85: end`

`  86: go`

`  87:`

`  88:`

I believe there can be other interesting datetime related calculations, like quarterly related datetime calculations, but should be similar to the queries mentioned above.