Tip of the Day : Example Uses of the DATEDIFF Date Function

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - April 30, 2017

Example Uses of the DATEDIFF Date Function

The DATEDIFF date function returns the number of date and time bourndaries crossed between two specified dates.  The syntax of the DATEDIFF date function is as follows:

DATEDIFF ( <datepart>, <startdate>, <enddate> )

The <datepart> parameter specifies on which part of the date to calculate the difference.  Valid values are YEAR or YYYY 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 <startdate> parameter is the starting date for the calculation and is an expression that returns a DATETIME or SMALLDATETIME value, or a character string in a date format.  The <enddate> parameter is the ending date for the calculation and 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 DATEDIFF date function:

Usage #1 : Calculate Age

DECLARE @BirthDate DATETIME = '1932/06/12'
SELECT DATEDIFF(YEAR, @BirthDate, GETDATE()) -
       CASE WHEN MONTH(@BirthDate) < MONTH(GETDATE()) OR
                (MONTH(@BirthDate) = MONTH(GETDATE()) AND DAY(@BirthDate) <= DAY(GETDATE()))
            THEN 0 ELSE 1 END AS [Age]

Usage #2 : Get Date Part of a DATETIME Value

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

Usage #3 : Get 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 #4 : Get 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 #5 : Get 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 #6 : Get 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 #7 : Get 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 #8 : Get 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]

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions