Date Calculations - SQL Server

General¶

declare @input datetime
set @input = '01/13/2010 12:31:00 pm'

select
     DateOnly           = convert(datetime, datediff(day, 0, @input))
    ,FirstDayThisMonth  = dateadd(day, 1-day(@input), @input)
    ,LastDayThisMonth   = dateadd(day, 0-day(@input), dateadd(month, 1, @input))
    ,FirstDayNextMonth  = dateadd(day, 1-day(@input), dateadd(month, 1, @input))

convert(date, getdate())

Special Situations¶

Combining a DATE and a TIME¶

create function dbo.CombineDateTime
    (
     @d date
    ,@t time
    ) returns datetime as begin

return DATEADD(ms, DATEDIFF(ms, 0, @t), CONVERT(smalldatetime, @d))

end

This Past Monday¶

declare 
     @baseDate datetime
    ,@today    datetime
    ,@n        int
    ,@result   datetime

select
    -- 1/1/1900 is a Monday 
     @baseDate  = '1/1/1900'

    -- Get the date-only (i.e., midnight this morning)
    ,@today     = convert(datetime, datediff(day, 0, getdate()))

    -- Count how many days since a known Monday.  This number MOD 7 will the number of 
    -- days since this past Monday. A result of zero means today is a Monday.
    ,@n         = (datediff(d, @baseDate, @today) % 7) * -1

    ,@result    = dateadd(d, @n, @today)

select result = @result

Other¶

/* Weeks run Monday to Sunday */

with Input as (
    select
         /* Use a date literal here to troublehshoot date calculations */
         --CurrentDate        = convert(date, '2010-02-07')
         CurrentDate        = convert(date, getutcdate())
    )
,cte as (
    select
         CurrentDate
        ,MinDate            = convert(date, '1901-01-01')
        ,MaxDate            = convert(date, '2199-12-31')

        /*--- Current Week ---*/
        ,CurrentWeekLo      = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7), CurrentDate))
        ,CurrentWeekHi      = convert(date, dateadd(day, 6-((datepart(weekday, CurrentDate) + 5) % 7), CurrentDate))

        /*--- Current Week, Days ---*/
        ,CurrentWeekMon     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 0, CurrentDate))
        ,CurrentWeekTue     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 1, CurrentDate))
        ,CurrentWeekWed     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 2, CurrentDate))
        ,CurrentWeekThu     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 3, CurrentDate))
        ,CurrentWeekFri     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 4, CurrentDate))
        ,CurrentWeekSat     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 5, CurrentDate))
        ,CurrentWeekSun     = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) + 6, CurrentDate))

        /*--- Last Week ---*/
        ,LastWeekLo         = convert(date, dateadd(day, 0-((datepart(weekday, CurrentDate) + 5) % 7) - 7, CurrentDate))
        ,LastWeekHi         = convert(date, dateadd(day, 6-((datepart(weekday, CurrentDate) + 5) % 7) - 7, CurrentDate))

        /*--- Current Month ---*/
        ,CurrentMonthLo     = convert(date, dateadd(day, 1-day(CurrentDate), CurrentDate))
        ,CurrentMonthHi     = convert(date, dateadd(day, -1, dateadd(month, 1, dateadd(day, 1-day(CurrentDate), CurrentDate))))

        /*--- Next Month ---*/
        ,NextMonthLo        = convert(date, dateadd(month, 1, dateadd(day, 1-day(CurrentDate), CurrentDate)))
        ,NextMonthHi        = convert(date, dateadd(day, -1, dateadd(month, 2, dateadd(day, 1-day(CurrentDate), CurrentDate))))

        /*--- Current Year ---*/
        ,CurrentYearLo      = convert(date, dateadd(day, 1-datepart(dy, CurrentDate), CurrentDate))
        ,CurrentYearHi      = convert(date, dateadd(day, -1, dateadd(year, 1, dateadd(day, 1-datepart(dy, CurrentDate), CurrentDate))))

        /*--- Future Years ---*/
        ,FutureYearsLo      = convert(date, dateadd(year, 1, dateadd(day, 1-datepart(dy, CurrentDate), CurrentDate)))

        /*--- Quarters ---*/
        ,Quarter1Lo         = convert(date, convert(varchar(10), year(CurrentDate)) + '-01-01')
        ,Quarter1Hi         = convert(date, convert(varchar(10), year(CurrentDate)) + '-03-31')
        ,Quarter2Lo         = convert(date, convert(varchar(10), year(CurrentDate)) + '-04-01')
        ,Quarter2Hi         = convert(date, convert(varchar(10), year(CurrentDate)) + '-06-30')
        ,Quarter3Lo         = convert(date, convert(varchar(10), year(CurrentDate)) + '-07-01')
        ,Quarter3Hi         = convert(date, convert(varchar(10), year(CurrentDate)) + '-09-30')
        ,Quarter4Lo         = convert(date, convert(varchar(10), year(CurrentDate)) + '-10-01')
        ,Quarter4Hi         = convert(date, convert(varchar(10), year(CurrentDate)) + '-12-31')

    from
        Input
    )
select
     cte.*

    /*--- Last Month ---*/
    ,LastMonthLo            = convert(date, dateadd(month, -1, CurrentMonthLo))
    ,LastMonthHi            = convert(date, dateadd(day, -1, CurrentMonthLo))

    /*--- Weeks of Current Month ---*/
    ,CurrentMonthWeek1Lo    = CurrentMonthLo
    ,CurrentMonthWeek1Hi    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 +  0, CurrentMonthLo)
    
    ,CurrentMonthWeek2Lo    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 +  1, CurrentMonthLo)
    ,CurrentMonthWeek2Hi    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 +  7, CurrentMonthLo)
    
    ,CurrentMonthWeek3Lo    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 +  8, CurrentMonthLo)
    ,CurrentMonthWeek3Hi    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 + 14, CurrentMonthLo)
    
    ,CurrentMonthWeek4Lo    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 + 15, CurrentMonthLo)
    ,CurrentMonthWeek4Hi    = dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 + 21, CurrentMonthLo)
    
    ,CurrentMonthWeek5Lo    = case when datepart(day, CurrentMonthHi) = 28 and datepart(weekday, CurrentMonthLo) = 2
                                then null
                                else dateadd(day, (8 - datepart(weekday, CurrentMonthLo)) % 7 + 22, CurrentMonthLo)
                                end
                            
    ,CurrentMonthWeek5Hi    = case when datepart(day, CurrentMonthHi) = 28 and datepart(weekday, CurrentMonthLo) = 2
                                then null
                                else CurrentMonthHi
                                end
        
from    
    cte