Calculating a Different Date

I'm sure we've all had a need to calculate dates.  Basically, taking the current date and doing some math on it, in order to derive another date.  For example, the first day of the month or week or quarter, the last day of this month or last, or even the last day of this year.  In this tip I will just show you a quick way to perform this type of calculation, using DATEADD, DATEPART and DATEDIFF:

DECLARE @today DATETIME

DECLARE @datefirst TINYINT

SET @today = CONVERT(CHAR(8), GETDATE(), 112) 

SET @datefirst = @@DATEFIRST

SET DATEFIRST 7

SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 1, @today) [1st Day of This Week]

SELECT DATEADD(DAY, -(DATEPART(WEEKDAY, @today)) + 7, @today) [Last Day of This Week]

SELECT DATEADD(DAY, -DATEPART(DAY, @today) + 1, @today) [1st Day of This Month]

SELECT DATEADD(qq, DATEDIFF(qq,0,@today), 0) [1st Day of This Qtr]

SELECT DATEADD(DAY, -DATEPART(DAY, @today), DATEADD(MONTH, 1, @today)) [Last Day of This Month]

SELECT DATEADD(MONTH, -1, DATEADD(DAY, - DAY(@Today) + 1, @Today)) [1st Day of Last Month]

SELECT DATEADD(DAY, - DAY(@Today), @Today) [Last Day of Last Month]

SELECT DATEADD(DAY, -DATEPART(DAYOFYEAR, @today) + 1, @today) [1st Day of This Year]

SELECT CAST(CAST(YEAR(@today) AS CHAR(4)) + '1231' AS DATETIME) [Last Day of This Year]

SET DATEFIRST @datefirst

The above is all very dynamic, based on @today.  This example will actually show you how to count the number of days, weekdays or even the hours, between two given dates, @StartDate and @EndDate:

DECLARE @StartDate DATETIME = '1/1/2011',

        @EndDate DATETIME = GETDATE()

/* Calculate # of Days between two dates */

SELECT DATEDIFF(dd,@StartDate,@EndDate) [Total # Days]

/* Calculate # of Weekdays between same two dates */

SELECT DATEDIFF(d,@StartDate,@EndDate)+1

- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@StartDate)=1 then 1 else 0 End )

- (DATEDIFF(wk,@StartDate,@EndDate) + CASE WHEN DATEPART(dw,@EndDate)=7 then 1 else 0 End ) [Total # Weekdays]

/* Calculate # of Hours between same two dates */

SELECT DATEDIFF(second,@startdate,@EndDate)/60.0/60.0 [Total # Hours]