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.

Syntax: DAY(date)

date operators in sql: Date Function DAY Example

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.

Syntax: MONTH(date)

date operators in sql: Date Function MONTH Example

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.

Syntax: YEAR(date)

date operators in sql: Date Function YEAR Example

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.

Syntax: EOMONTH(start_date [, month_to_add ])

date operators in sql: Date Function EOMONTH() Example

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).

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

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.


date operators in sql: Example of DATEADD

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.

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

date operators in sql: Date Function CONVERT Example

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.

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

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.


date operators in sql: Date Function CONVERT Example

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.

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

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.


date operators in sql: Date Function DATEDIFF Example

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.

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

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

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

Example of FORMAT(): M – month name

Example of FORMAT(): M – month name

Example of FORMAT(): y – year mask

Example of FORMAT(): y – year mask

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

Example of FORMAT-time parts

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.


Example of FORMAT Putting it all together

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

The given below illustration shows the optional culture parameter.


Example of FORMAT using cultures for US and England

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.

Syntax: CAST(expression AS data_type)

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


Date Function CAST

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.

Syntax: ISDATE(date string)

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.

Example of ISDATE

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

Using conditional logic for IF and CASE statements

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.