SQL DATE Functions

Problem

In this tutorial we will explore several SQL Date Functions and show many examples of the date functions usage.

Solution

Below is a list of SQL Date functions with descriptions, followed by several examples of each.  Hopefully, this will serve as a good quick reference when working with dates in T-SQL coding.

List of SQL DATE Functions

The DATE functions in the following table will be presented in this article.

Date Function DAY()

The date function DAY accepts a date, datetime, or valid date string and returns the Day part as an integer value.

Syntax: DAY(date)

--Example of DAY(): 
SELECT GETDATE(), DAY(GETDATE()) , DAY('20210101'), DAY('2021-05-30 15:46:19.277');
GO

Results:

Date Function MONTH()

The date function MONTH accepts a date, datetime, or valid date string and returns the Month part as an integer value.

Syntax: MONTH(date)

--Example of MONTH(): 
SELECT GETDATE(), MONTH(GETDATE()) , MONTH('20210101'), MONTH('2021-05-30 15:46:19.277');
GO

Results:

Date Function YEAR()

The date function YEAR accepts a date, datetime, or valid date string and returns the Year part as an integer value.

Syntax: YEAR(date)

--Example of YEAR(): 
SELECT GETDATE(), YEAR(GETDATE()) , YEAR('20210101'), YEAR('2021-05-30 15:46:19.277');
GO

Results:

Date Function EOMONTH()

The date function EOMONTH accepts a date, datetime, or valid date string and returns the end of month date as a datetime. It can also take an optional offset that basically adds or subtracts months from the current passed date.

Syntax: EOMONTH(start_date [, month_to_add ])

--Example of EOMONTH(): Shows different date formats being passed in.
SELECT EOMONTH(GETDATE()), EOMONTH('20210101'), EOMONTH('May 1, 2021');
 
--Example of EOMONTH(): Shows the use of the offset optional parameter with the GETDATE function which is the current date
SELECT EOMONTH(GETDATE()) as 'End Of Current Month',
   EOMONTH(GETDATE(),-1) as 'End Of Last Month',
   EOMONTH(GETDATE(),6) as 'End Of Month +6';
GO

Results:

Date Function DATEADD

The date function DATEADD accepts a date part, a number to add, date, datetime, or valid date string and returns datetime result based on the units add (can be negative).

Syntax: DATEADD(date part, units, date or datetime)

Date Parts: can use the name or listed abbreviations:

• year, yy, yyyy
• quarter, qq, q
• month, mm, m
• dayofyear, dy, y*
• day, dd, d*
• weekday, dw, w*
• week, wk, ww
• hour, hh
• minute, mi, n
• second, ss, s
• millisecond, ms
• microsecond, mcs
• nanosecond, ns

*Note: dayofyear, day, and weekday return the same value.

--Example of DATEADD():
SELECT DATEADD(DAY,1,'2021-01-01') as 'Add 1 Day',
   DATEADD(WEEK,1,'2021-01-01') as 'Add 1 Week',
   DATEADD(MONTH,1,'2021-01-01') as 'Add 1 Month',
   DATEADD(YEAR,1,'2021-01-01') as 'Add 1 Year';
GO

Results:

Date Function DATEDIFF

The date function DATEDIFF accepts a date part, start date and end date as date datetime, or valid date string and returns the difference between the dates in units bast on the date part specified.

Syntax: DATEDIFF (date part, start date, end date)

Date Parts: can use the name or listed abbreviations:

• year, yy, yyyy
• quarter, qq, q
• month, mm, m
• dayofyear, dy, y
• day, dd, d
• week, wk, ww
• hour, hh
• minute, mi, n
• second, ss, s
• millisecond, ms
• microsecond, mcs
• nanosecond, ns

*The following example will use the date part names rather than the abbreviation.

--Example of DATEDIFF():
SELECT DATEDIFF(DAY,'2021-01-01','2021-02-01') as 'Number of Days in January',
   DATEDIFF(WEEK,'2021-01-01','2022-01-01') as 'Weeks in the Year',
   DATEDIFF(MONTH,'2021-01-01','2022-01-01') as 'Months in the Year',
   DATEDIFF(YEAR,'1900-01-01',GETDATE()) as 'Years Since 1900';
GO

Results:

To learn more on DATEDIFF Read tip: DATEDIFF SQL Server Function.

Date Function DATEDIFF_BIG

The DATEDIFF_BIG function is used in the same way as the DATEDIFF function.  However, the DATEDIFF_BIG function is typically used with date parts:  millisecond, microsecond, and nanosecond when the return value exceeds the range of integer (-2,147,483,648 to +2,147,483,647) thus requiring the return value as a BIGINT data type.

Syntax: DATEDIFF_BIG(date part, start date, end date)

Date Parts: can use the name or listed abbreviations:

• year, yy, yyyy
• quarter, qq, q
• month, mm, m
• dayofyear, dy, y
• day, dd, d
• week, wk, ww
• hour, hh
• minute, mi, n
• second, ss, s
• millisecond, ms
• microsecond, mcs
• nanosecond, ns

*The following example will use the datepart names.

--Example of DATEDIFF_BIG():
SELECT DATEDIFF_BIG(MILLISECOND, '01-01-2020', '01-01-2021') as 'Milliseconds in a Year'
SELECT DATEDIFF_BIG(NANOSECOND, '01-01-2020', '01-01-2021') as 'Nanoseconds in a Year'
GO

Results:

To learn more on DATEDIFF_BIG Read tip: DATEDIFF SQL Server Function

Date Function CONVERT

The Convert function is used to convert data to different data types and can format the output string.  In this example we used CONVERT to format the output datetime value as a character string.

Syntax: CONVERT( data_type [ ( length ) ] , expression [ , style ] )

--Example of CONVERT():
SELECT CONVERT(CHAR(19), GETDATE(), 100) as 'Mon dd YYYY hh:mmAM';
 
SELECT CONVERT(CHAR(8), GETDATE(), 112) as 'YYYYMMDD';
 
SELECT CONVERT(CHAR(20), GETDATE(), 22) as 'mm/dd/yy hh:mi:ss AM';
 
SELECT CONVERT(CHAR(10),GETDATE(),120) as 'MyDate_w_Dash',
   CONVERT(CHAR(10),GETDATE(),111) as 'MyDateTime_w_Slash',
   CONVERT(CHAR(10),GETDATE(),102) as 'MyDateTime_w_Dot';
GO

Results:

To learn more on CONVERT Read tip:

• SQL Convert Date to YYYYMMDD
• Date and Time Conversions Using SQL Server

Date Function FORMAT

The FORMAT function returns a nvarchar value, the length determined by the specified format. The FORMAT function converts numeric and date time data types. In these examples we will only be focusing on Dates.

Per docs.microsoft.com the format argument must contain a valid .NET Framework format string, either as a standard format string (for example, "C" or "D"), or as a pattern of custom characters for dates and numeric values (for example, "MMMM DD, yyyy (dddd)").  For more details from Microsoft refer to: Custom date and time format strings.

Syntax: FORMAT( value, format [, culture ] )

Format: here is a list of the custom date and time format specifiers: "d", "f", "F", "g", "h", "H", "K", "m", "M", "s", "t", "y", "z", ":", or "/" .

*Note: Lower case d and y, and Upper case M and D, return addition date info.

The examples below show the results of different groups of format specifiers for day, month, year, and time.  Also below is an example of combining the many of the format specifiers to form a complete date and time string, and an example of the culture argument.

Example of FORMAT(): D, d – day mask

--DAY
DECLARE @date DATETIME = '01/01/2021';
SELECT    'D' as 'Mask', FORMAT(@date, 'D')    as 'Result'
SELECT    'd' as 'Mask', FORMAT(@date, 'd')    as 'Result'
SELECT   'dd' as 'Mask', FORMAT(@date, 'dd')   as 'Result'
SELECT  'ddd' as 'Mask', FORMAT(@date, 'ddd')  as 'Result'
SELECT 'dddd' as 'Mask', FORMAT(@date, 'dddd') as 'Result'
go

Results:

Example of FORMAT(): M – month name

--MONTH (Upper case)
DECLARE @date DATETIME = '01/01/2021';
SELECT    'M' as 'Mask', FORMAT(@date, 'M')    as 'Result'
SELECT   'MM' as 'Mask', FORMAT(@date, 'MM')   as 'Result'
SELECT  'MMM' as 'Mask', FORMAT(@date, 'MMM')  as 'Result'
SELECT 'MMMM' as 'Mask', FORMAT(@date, 'MMMM') as 'Result'
GO

Results:

Example of FORMAT(): y – year mask

--YEAR
DECLARE @date DATETIME = '01/01/2021';
SELECT    'y' as 'Mask', FORMAT(@date, 'y')    as 'Result'
SELECT  'yyy' as 'Mask', FORMAT(@date, 'yy')   as 'Result'
SELECT 'yyyy' as 'Mask', FORMAT(@date, 'yyyy') as 'Result'
GO

Results:

Example of FORMAT(): h,m,s,t, f, F - time parts

--TIME
DECLARE @date DATETIME = GETDATE();
SELECT 'hh:mm tt' as 'Mask', FORMAT(@date, 'hh:mm tt')
SELECT 'h:m:s.F t' as 'Mask', FORMAT(@date, 'h:m:s.F t')
SELECT 'hh:mm:ss.ff tt' as 'Mask', FORMAT(@date, 'hh:mm:ss.ff tt')
SELECT 'HH:mm:ss.ff' as 'Mask', FORMAT(@date, 'HH:mm:ss.ff')
go

Results:

Example of FORMAT(): Putting it all together

This example combines many of the format specifiers including: AM/PM, timezone off set [zz] and culture = Germany.

--Put it All Together with AM/PM, timezone off set [zz] and culture = Germany.
DECLARE @date DATETIME = '01/01/2021';
SELECT FORMAT(@date, 'dddd, MMMM dd, yyyy hh:mm:ss.ff tt') as 'All together'
SELECT FORMAT(@date, 'dddd, MM/dd/yyyy hh:mm tt (zz)') as 'With Timezone Offset'
SELECT FORMAT(@date, 'dddd, dd MMMM yyyy hh:mm tt (zz)','de-de') as 'With Timezone Offset in German'
GO

Results:

Example of FORMAT():  using cultures for US and England

This example shows the optional culture parameter.

DECLARE @d DATE = '20211231';
SELECT FORMAT( @d, 'd', 'en-US' ) 'US'  
      ,FORMAT( @d, 'd', 'en-gb' ) 'GBR';  
  
SELECT FORMAT( @d, 'D', 'en-US' ) 'US'  
      ,FORMAT( @d, 'D', 'en-gb' ) 'GBR' 
GO

Results:

To learn more on FORMAT Read tips:

• SQL Convert Date to YYYYMMDD
• Format SQL Server Dates with FORMAT Function

Date Function CAST

CAST is used similar to the CONVERT function, to change date types.  For dates you typically can use CAST to either change the data type of string to a date data type or change a date to character data type.

Syntax: CAST(expression AS data_type)

Example of CAST():  Compare cast and convert and basic Cast to date and datetime.

--1. Example: CAST() vs CONVERT:
SELECT GETDATE() AS [DateTime],  
   CAST(GETDATE() AS NVARCHAR(30)) AS [With Cast],  
   CONVERT(nvarchar(30), GETDATE(), 100) AS[With ConvertTo_100]  ;  
GO 
 
--2. Example: Cast to data types DATE and DATETIME.
SELECT CAST('1 Jan 2021' AS DATE) AS [1.String to Date], 
   CAST('20210101' AS DATE) AS [2.String to Date],
   CAST('1/1/2021' AS DATETIME) AS [3.String to Datetime],
   CAST('2021-01-01 12:00:55.520' AS DATETIME) AS [4.String to Datetime];
GO

Results:

Date Function ISDATE

Use ISDATE function to check a string to see if it is a valid Date or Datetime field. ISDATE return 1 if true or 0 if false.

Syntax: ISDATE(date string)

Note: The results of the ISDATE function will be affected by the default LANGUAGE and DATEFORMAT settings! To see your current LANGUAGE and DATEFORMAT settings you can run command: DBCC USEROPTIONS. Also, to see the Language setting you can SELECT @@LANGUAGE.

The following examples assumes the Language setting is set to us_english!

Example of ISDATE():  Basic isdate() example.

--Example of ISDATE():
SELECT ISDATE('20210101') as 'Valid';  
SELECT ISDATE('01/01/21') as 'Valid'; 
SELECT ISDATE('13/01/2021') as 'Not Valid'; 
GO

Results:

Example of ISDATE():  Using conditional logic for “IF” and CASE statements.

--1. Use ISDATE to Validate a potential date string.
DECLARE @CharDate CHAR(10) = '20210101';
IF ISDATE(@CharDate) = 1
   SELECT CAST(@CharDate as DATETIME) 'Valid Date'
ELSE
   SELECT 'INVALID' as 'Invalid Date';
--2. Example set to an invalid date string
SET @CharDate = '202101xx';
IF ISDATE(@CharDate) = 1
   SELECT CAST(@CharDate as DATETIME) 'Valid Date'
ELSE
   SELECT 'INVALID' as 'Invalid Date';
--3. Use in a CASE Statement
SET @CharDate = '2021-05-30';
SELECT CASE ISDATE(@CharDate) 
         WHEN 0 THEN NULL
         ELSE CAST(@CharDate as DATETIME)
      END as 'ISDATE w/ CASE';
GO

Results:

Next Steps

This concludes the SQL Date functions tip.  Hopefully this will be a useful reference when using Date function in T-SQL.

• For more information on Date functions please review the MSSQLTips I referenced throughout this article.
• Search MSSQLTips for by date or function name for other articles.
• Review these addition article references:
  • SQL Server Date and Time Functions with Examples
  • New Date and Time Functions in SQL Server
  • SQL Convert Date to YYYYMMDD
  • Format SQL Server Dates with FORMAT Function
  • DATEDIFF SQL Server Function
  • All SQL Server Date Tips