SQL DATE Functions


By:   |   Updated: 2021-05-18   |   Comments   |   Related: More > Dates


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 Functions Desc Return Value Data Type Note
DAY (date or datetime) Returns the day of the week for a given date Integer like 1 - 31  
MONTH (date or datetime) Returns the month of a given date Integer like 1 - 12  
YEAR (date or datetime) Returns the year of a given date Integer for year like 2021  
DATEPART (date part, date or datetime) Returns the date part specified in int format Integer like 1 – 12 for month, 1 – 31 for day, or year like 2021  
DATENAME (date part, date or datetime) Returns the date part specified in character format Character like April, May, ‘1’, ‘2’, ‘31’, ‘2020’, ‘2021’  
EOMONTH (date [,months to add) Returns the last do of the month with an optional parameter to add months (+ or -). 01/31/2021 Introduced in SQL Server 2012.
DATEADD (date part, units, date or datetime) Return date math results datetime  
DATEDIFF (date part, start date, end date) Give the difference between 2 dates in units specified by date part Integer of date part units  
DATEDIFF_BIG Give the difference between 2 dates in units specified by date part Big Integer of date part units  
CONVERT (date type, value  [ , style ] Used to convert date output to a specified mask Typically, a character datatype is specified when converting dates.  *To convert a valid date char string to date no function is needed! Implicit convert!
FORMAT ( value, format [, culture ] ) Used to convert date output to a specified mask Returns a date formatted string based on the mask specified.  
CAST (value as data type) Used to convert different data types to a date or datetime data type. Returns data in the data type specified.  
ISDATE (potential date string) Use to validate a date string Returns 1 if the string is a valid date or 0 if not a valid date.  

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:

query 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:

query 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:

query 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:

query 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:

query 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:

query 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:

query 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:

query results

To learn more on CONVERT Read tip:

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:

query 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:

query 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:

query 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:

query 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:

query 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:

query results

To learn more on FORMAT Read tips:

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:

query 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:

query 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:

query results
Next Steps

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






get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager currently with Harsco who has managed DBAs, Application and BI Developers and Data Management teams for over 20 years.

View all my tips


Article Last Updated: 2021-05-18

Comments For This Article





download














get free sql tips
agree to terms