Quick Search
»

 Create a new Page Administration File Management Login/Logout Your Profile

# Date Calculations - SQL Server

Modified on Thu, Apr 01, 2010, 12:09 PM Categorized as 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```

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2019, Patrick Jasinski.