Date Calculations - SQL Server

General¶

{copytext|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))

The following code will work with SQL Server 2008 and newer, which has the new data type, DATE.

convert(date, getdate())

Special Situations¶

Combining a DATE and a TIME¶

{copytext|CombineDateTime}

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¶

{copytext|thisPastMonday}

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