Quick Search
»

# Date Calculations - SQL Server

Modified on Thu, Mar 28, 2024, 7:50 AM 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))

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

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

/*--- Next Month ---*/

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

/*--- Future Years ---*/

/*--- 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
```

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