Date Operators in SQL | What is SQL Date Functions With Examples
Summary: In this tutorial, we’re going to cover what Date Operators in SQL and how to use SQL Date Functions with practical examples. We have numerous in-built functions in SQL, that facilitate us to filter helpful data from a large set of data. Furthermore, one such part is the date and time functions of SQL.
SQL Date Functions | Introduction of Date operators in SQL
Working with the database isn’t very difficult as one needs to understand the basic queries and keywords that should be used to update, delete and retrieve the data in the database.
Furthermore, It allows us to store the structured data (This data will be in the form of rows and columns) and provides easy queries to work on it. during this topic, we are going to discuss the SQL Date Function.
The date function that is used in the SQL Server, to know as there are several formats in which data can be stored in the database, and in different formats, the user needs to retrieve it depending on the particular requirements.
In SQL, DateTime is frequently used to store both the date and time values at a time in a single column.
Furthermore, DateTime (time is additionally used alongside the date) is often wont to store each the date and time values at a time in a very single column.
List of SQL DATE Functions
Date Functions | Description | Return Value Data Type |
---|---|---|
DAY (date or datetime) | It will return the day of the week for a given date | Integer such as 1 – 31 |
MONTH (date or datetime) | It will return the month of a given date | Integer such as 1 – 12 |
YEAR (date or datetime) | It will return the year of a given date | Integer for year such as 2020 |
DATEPART (date part, date or datetime) | It will return the date part specified in int format | Integer such as 1 – 12 for month, 1 – 31 for day, or year like 2020 |
DATENAME (date part, date or datetime) | It will return the date part specified in character format | Character such as Jun, July, ‘1’, ‘2’, ‘31’, ‘2020’, ‘2021’ |
EOMONTH (date [,months to add) | It will return the last do of the month with an optional parameter to add months (+ or -). | 11/28/2020 |
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 the date part | Integer of date part units |
DATEDIFF_BIG | Give the difference between 2 dates in units specified by the date part | Big Integer of date part units |
CONVERT (date type, value [ , style ] | It will be used to convert date output to a specified mask | Typically, a character data type is specified when converting dates. |
FORMAT ( value, format [, culture ] ) | It will be used to convert date output to a specified mask | Returns date formatted string based on the mask specified. |
CAST (value as data type) | Used to convert different data types to date or DateTime data types. | 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. |
SQL Date Functions Example
Date Function DAY()
The date function DAY accepts a date, datetime, or valid date string & returns the Day part as an integer value.
1 2 3 |
--Example of DAY(): SELECT GETDATE(), DAY(GETDATE()) , DAY('20210101'), DAY('2021-05-30 15:46:19.277'); GO |
Date Function MONTH()
The date function MONTH in SQL will accept a date, datetime, or valid date string & returns the Month part as an integer value.
1 2 3 |
--Example of MONTH(): SELECT GETDATE(), MONTH(GETDATE()) , MONTH('20210202'), MONTH('2021-07-30 16:46:19.277'); GO |
Date Function YEAR()
The date function YEAR will accept a date, datetime, or valid date string and returns the Year partly as an integer value.
1 2 3 |
--Example of YEAR(): SELECT GETDATE(), YEAR(GETDATE()) , YEAR('20210202'), YEAR('2021-07-30 16:46:19.277'); GO |
Date Function EOMONTH()
The SQL date function EOMONTH will accept a date, datetime, or valid date string and thus it returns the end of month date as a datetime. Furthermore, it can also take an optional offset that essentially adds(+) or subtracts(-) months from the current passed to date.
1 2 3 4 5 6 7 8 9 |
--Example of EOMONTH(): Shows different date formats being passed in. SELECT EOMONTH(GETDATE()), EOMONTH('20210202'), EOMONTH('Jun 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(),-2) as 'End Of Last Month', EOMONTH(GETDATE(),8) as 'End Of Month +8'; GO |
Date Function DATEADD
The SQL date function DATEADD will accept a date part, a number to add, date, and datetime, or a valid date string & it returns datetime result supported the units add (can be negative).
Date Parts: will 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, & weekday return the identical value.
1 2 3 4 5 6 |
--Example of DATEADD(): SELECT DATEADD(DAY,1,'2021-02-02') as 'Add 1 Day', DATEADD(WEEK,1,'2021-02-02') as 'Add 1 Week', DATEADD(MONTH,1,'2021-02-02') as 'Add 1 Month', DATEADD(YEAR,1,'2021-02-02') as 'Add 1 Year'; GO |
Date Function CONVERT:- Date Operators In SQL
The Convert function in SQL is used to convert data to different data types & can format the output string. In this demonstration, we used CONVERT to format the output datetime value as a character string.
1 2 3 4 5 6 7 8 9 10 11 |
--Example of CONVERT(): SELECT CONVERT(CHAR(21), GETDATE(), 100) as 'Mon dd YYYY hh:mmAM'; SELECT CONVERT(CHAR(18), GETDATE(), 112) as 'YYYYMMDD'; SELECT CONVERT(CHAR(22), GETDATE(), 22) as 'mm/dd/yy hh:mi:ss AM'; SELECT CONVERT(CHAR(11),GETDATE(),120) as 'MyDate_w_Dash', CONVERT(CHAR(11),GETDATE(),111) as 'MyDateTime_w_Slash', CONVERT(CHAR(11),GETDATE(),102) as 'MyDateTime_w_Dot'; GO |
Date Function DATEDIFF_BIG
The DATEDIFF_BIG function in SQL Server is used in the same way as the DATEDIFF function. Furthermore, the DATEDIFF_BIG function is usually utilized with date parts: like a millisecond, microsecond, & nanosecond. but when the return value exceeds the range of integer (-2,147,483,648 to +2,147,483,647) Thus it requires to return value as a BIGINT data type.
Date Parts: Therefore, it can use the name or The 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 illustration will use the datepart names.
1 2 3 4 |
--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 |
Date Function DATEDIFF
The DATEDIFF is also a date function that can accept a date part, start date, & end date as date datetime, or valid date string & it returns the difference b/w the dates in units based on the date part specified.
Date Parts: Therefore, it can use the name or The 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 given below demonstration will use the date part names rather than the abbreviation.
1 2 3 4 5 6 |
--Example of DATEDIFF(): SELECT DATEDIFF(DAY,'2020-01-01','2020-02-01') as 'Number of Days in January', DATEDIFF(WEEK,'2020-01-01','2021-01-01') as 'Weeks in the Year', DATEDIFF(MONTH,'2020-01-01','2021-01-01') as 'Months in the Year', DATEDIFF(YEAR,'1992-01-01',GETDATE()) as 'Years Since 1992'; GO |
Date Function FORMAT | Date Operators In SQL
The FORMAT function in SQL returns a nvarchar value, the length determined by the specified format. Therefore, the FORMAT function converts numeric & date-time data types. In this demonstration, we will only be focusing on Dates.
Therefore, as per docs.microsoft.com, the format argument must contain a valid .NET Framework format string, either as a standard format string or as a pattern of custom characters for dates and numeric values.
Format: the custom date & time format specifiers list: “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 given below examples are the results of different groups of format specifiers for the day, month, year, and time. Furthermore, also below is an example of combining many of the format specifiers to form a complete date & time string, and an example of the culture argument.
Example of FORMAT(): D, d – day mask
1 2 3 4 5 6 7 8 |
--DAY DECLARE @date DATETIME = '01/01/2020'; 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 |
Example of FORMAT(): M – month name
1 2 3 4 5 6 7 |
--MONTH (Upper case) DECLARE @date DATETIME = '01/01/2020'; 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 |
Example of FORMAT(): y – year mask
1 2 3 4 5 6 |
--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 |
Example of FORMAT(): h,m,s,t, f, F – time parts
1 2 3 4 5 6 7 |
--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 |
Example of FORMAT(): Putting it all together
This demonstration combines many of the format specifiers including like AM/PM, timezone off set [zz] & culture = Germany.
1 2 3 4 5 6 |
--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 |
Example of FORMAT(): using cultures for US and England
The given below illustration shows the optional culture parameter.
1 2 3 4 5 6 7 |
DECLARE @d DATE = '20200928'; 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 |
Date Function CAST
Date Operators in SQL:- CAST is used identical to the SQL 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 date to character data type.
Example of CAST(): Compare cast and convert and basic Cast to date and datetime.
1 2 3 4 5 6 7 8 9 10 11 12 |
--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 2020' AS DATE) AS [1.String to Date], CAST('20200101' AS DATE) AS [2.String to Date], CAST('1/1/2020' AS DATETIME) AS [3.String to Datetime], CAST('2020-01-01 11:00:55.520' AS DATETIME) AS [4.String to Datetime]; GO |
Date Function ISDATE
To Use ISDATE SQL function to check a string to see if it is a valid Date or Datetime field. ISDATE returns 1 if true or 0 if false.
Important Note: Date Operators in SQL- The results of the SQL Server ISDATE function can be affected by the default LANGUAGE & DATEFORMAT settings! To see your current LANGUAGE & DATEFORMAT settings you can run the command: DBCC USER OPTIONS. Also, to see the Language setting you can SELECT @@LANGUAGE.
The given below following examples assume the Language setting is set to us_english!
Example of ISDATE(): Basic isdate() example.
1 2 3 4 5 |
--Example of ISDATE(): SELECT ISDATE('20200101') as 'Valid'; SELECT ISDATE('01/01/20') as 'Valid'; SELECT ISDATE('13/01/2020') as 'Not Valid'; GO |
Example of ISDATE(): Using conditional logic for “IF” and CASE statements
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
--1. Use ISDATE to Validate a potential date string. DECLARE @CharDate CHAR(10) = '20200101'; 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 = '202001xx'; 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 = '2020-05-30'; SELECT CASE ISDATE(@CharDate) WHEN 0 THEN NULL ELSE CAST(@CharDate as DATETIME) END as 'ISDATE w/ CASE'; GO |
Conclusion:
In this article, You have learned Date Operators in SQL and use of SQL Date Functions with practical examples. I would say, that go ahead & practice for more examples on the different types of operators to get good practice on writing SQL queries.