Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Example Uses of the SYSDATETIME Date Function
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

The DATEADD date function returns a new DATETIME value based on adding an interval to the specified date. The syntax of the DATEADD date function is as follows:

DATEADD ( <datepart>, <number>, <date> )

The <datepart> parameter specifies on which part of the date to return a new value. Valid values are YEAR or YY, QUARTER or QQ or Q, MONTH or MM or M, DAYOFYEAR or DY or Y, DAY or DD or D, WEEK or WK or WW, WEEKDAY or DW or W, HOUR or HH, MINUTE or MI or N, SECOND or SS or S and MILLISECOND or ms.

The <number> parameter is the value used to increment the <datepart>. The <date> parameter is an expression that returns a DATETIME or SMALLDATETIME value, or a character string in a date format.

Here are a few uses of the DATEADD date function:

Usage #1 : Get the Date Part of a DATETIME Value

SELECT DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), 0) AS [Date Part Only]

Usage #2 : Get the First Day of the Month, Quarter and Year

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) AS [First Day of the Month]
SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0) AS [First Day of the Quarter]
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) AS [First Day of the Year]

Usage #3 : Get the Last Day of the Month, Quarter and Year

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) - 1 AS [Last Day of the Month]
SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) + 1, 0) - 1 AS [Last Day of the Quarter]
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) - 1 AS [Last Day of the Year]

Usage #4 : Get the First Day of the Following Month, Quarter and Year

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 1, 0) AS [First Day of Next Month]
SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) + 1, 0) AS [First Day of Next Quarter]
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0) AS [First Day of Next Year]

Usage #5 : Get the Last Day of the Following Month, Quarter and Year

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) + 2, 0) - 1 AS [Last Day of Next Month]
SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) + 2, 0) - 1 AS [Last Day of Next Quarter]
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 2, 0) - 1 AS [Last Day of Next Year]

Usage #6 : Get the First Day of the Previous Month, Quarter and Year

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()) - 1, 0) AS [First Day Of Previous Month]
SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()) - 1, 0) AS [First Day Of Previous Quarter]
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) - 1, 0) AS [First Day Of Previous Year]

Usage #7 : Get the Last Day of the Previous Month, Quarter and Year

SELECT DATEADD(MM, DATEDIFF(MM, 0, GETDATE()), 0) - 1 AS [Last Day of Previous Month]
SELECT DATEADD(Q, DATEDIFF(Q, 0, GETDATE()), 0) - 1 AS [Last Day of Previous Quarter]
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0) - 1 AS [Last Day of Previous Year]

 

Back to Tip of the Day List Next Tip