Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software to obtain a set of commonly used relative dates such as last month or last year. The CTE can be transported and integrated into other queries or sprocs.

USE AdventureWorks

GO

WITH cteRelativeDates

AS

(

SELECT      getdate() AS CurrentTime,

CONVERT(VARCHAR, DATEADD(DAY, DATEDIFF(DAY, 1, getdate()), 0),110) AS Yesterday,

CONVERT(VARCHAR, DATEADD(DAY, DATEDIFF(DAY, 0, getdate()), 0),110) AS Today,

CONVERT(VARCHAR,DATEADD(WW, DATEDIFF(WW, 7, getdate()), 0),110) AS LastWeek,

CONVERT(VARCHAR,DATEADD(WW, DATEDIFF(WW, 0, getdate()), 0),110) AS ThisWeek,

CONVERT(VARCHAR,DATEADD(WW, DATEDIFF(WW, 0, getdate()), 0),110) AS Monday,

CONVERT(VARCHAR,DATEADD(WW, DATEDIFF(WW, 0, getdate()), 4),110) AS Friday,

CONVERT(VARCHAR,DATEADD(WW, DATEDIFF(WW, 0, getdate()), 7),110) AS NextWeek,

CONVERT(VARCHAR,DATEADD(MM, DATEDIFF(MM, 31, getdate()), 0),110) AS LastMonth,

CONVERT(VARCHAR,DATEADD(MM, DATEDIFF(MM, 0, getdate()), 0),110) AS CurrentMonth,

CONVERT(VARCHAR,DATEADD(MM, DATEDIFF(MM, -1, getdate()), 0),110) AS NextMonth,

CONVERT(VARCHAR,DATEADD(QQ, DATEDIFF(QQ, 92, getdate()), 0),110) AS LastQuarter,

CONVERT(VARCHAR,DATEADD(QQ, DATEDIFF(QQ, 0, getdate()), 0),110) AS CurrentQuarter,

CONVERT(VARCHAR,DATEADD(QQ, DATEDIFF(QQ, -1, getdate()), 0),110) AS NextQuarter,

CONVERT(VARCHAR,DATEADD(YY, DATEDIFF(YY, 365, getdate()), 0),110) AS LastYear,

CONVERT(VARCHAR,DATEADD(YY, DATEDIFF(YY, 0, getdate()), 0),110) AS CurrentYear,

CONVERT(VARCHAR,DATEADD(YY, DATEDIFF(YY, -1, getdate()), 0),110) AS NextYear

)

SELECT * FROM cteRelativeDates