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.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

4 Responses to Fun in DateTime Calculations

  1. Jason Root says:

    These are great – date arithmetic always gives me a headache!

    One thing though, the calculations for ‘first weekend day of the month’ are wrong. It works for the previous month, but not for current or next month – the calculation in the ‘then’ part of the case is incorrect. For current month it needs to be:
    then dateadd(month, datediff(month, 0, @dt),0)+ 6 – datepart(dw, dateadd(month, datediff(month, 0, @dt),0)+@@datefirst-1)
    and for next month it needs to be:
    then dateadd(month, datediff(month, 0, @dt)+1,0)+ 6 – datepart(dw, dateadd(month, datediff(month, 0, @dt)+1,0)+@@datefirst-1)

    It looks like a copy+paste issue on the ‘datepart’ section…

  2. Carl says:

    This is really enlightening. I see now that every day has a number. Once you realize that January 1, 1900 is day zero, all the calculations can be done from there. Thanks.

  3. Nice post! I’m going to add the best parts to my collection:

    SELECT 0 AS ZeroDate,
    SYSDATETIME() AS CurrentSystemDateTime,
    GETDATE() AS CurrentDateTime,
    CAST(DATEADD(DAY, DATEDIFF(DAY, 0, SYSDATETIME()), 0) AS DATE) AS CurrentDate,
    DATEDIFF(YEAR, 0, SYSDATETIME()) AS CurrentSystemYear,
    DATEDIFF(QUARTER, 0, SYSDATETIME()) AS CurrentSystemQuarter,
    DATEDIFF(MONTH, 0, SYSDATETIME()) AS CurrentSystemMonth,
    DATEDIFF(WEEK, 0, SYSDATETIME()) AS CurrentSystemWeek,
    DATEDIFF(DAY, 0, SYSDATETIME()) AS CurrentSystemDay,
    DATEDIFF(MINUTE, 0, SYSDATETIME()) AS CurrentSystemMinute,
    —————————————————————————————————
    YEAR(GETDATE()) AS CurrentYear,
    MONTH(GETDATE()) AS CurrentMonth,
    DAY(GETDATE()) AS CurrentDay,
    —————————————————————————————————
    DATEADD(YEAR, DATEDIFF(YEAR, 0, SYSDATETIME()), 0) AS FirstDayCurrentYear,
    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, SYSDATETIME()), 0) AS FirstDayCurrentQuarter,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()), 0) AS FirstDayCurrentMonth,
    DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()), 0) AS FirstDayCurrentWeek,
    —————————————————————————————————
    DATEADD(YEAR, DATEDIFF(YEAR, -1, SYSDATETIME()), -1) AS LastDayCurrentYear,
    DATEADD(QUARTER, DATEDIFF(QUARTER, -1, SYSDATETIME()), -1) AS LastDayCurrentQuarter,
    DATEADD(MONTH, DATEDIFF(MONTH, -1, SYSDATETIME()), -1) AS LastDayCurrentMonth,
    DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()) + 1, 0) – 1 AS LastDayCurrentWeek,
    —————————————————————————————————

    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, SYSDATETIME())-1, 0) AS FirstDayLastFullQuarter,
    DATEADD(QUARTER, DATEDIFF(QUARTER, -1, SYSDATETIME())-1, -1) AS LastDayLastFullQuarter,
    —————————————————————————————————
    DATEADD(YEAR, DATEDIFF(YEAR, 0, SYSDATETIME())-1, 0) AS FirstDayPreviousYear,
    DATEADD(QUARTER, DATEDIFF(QUARTER, 0, SYSDATETIME())-1, 0) AS FirstDayPreviousQuarter,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME())-1, 0) AS FirstDayPreviousMonth,
    DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME())-1, 0) AS FirstDayPreviousWeek,
    —————————————————————————————————
    DATEADD(DAY,-1,DATEADD(YEAR, DATEDIFF(YEAR, 0, SYSDATETIME()), 0)) AS LastDayPreviousYear,
    DATEADD(DAY,-1,DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()), 0)) AS LastDayPreviousMonth,
    DATEADD(WEEK, DATEDIFF(WEEK, -1, SYSDATETIME()), -1) AS LastDayPreviousWeek,

    DATEADD(MONTH, DATEDIFF(MONTH, 0, SYSDATETIME()) + 1, 0) AS FirstDayNextMonth,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) – 1, 0) AS LastFullMonthStartDate,
    DATEADD(DAY,-DATEPART(DAY,GETDATE()),GETDATE()) AS LastFullMonthEndDate,
    —————————————————————————————————
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-12, 0) AS LastFull12MonthsStartDateUseGTorEq2,
    DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0) AS LastFull12MonthsEndDateUseLT

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s