SQL Date Functions – Important

sql-date-functions

SQL date functions are important to learn if you want to do anything related to-
Forecasting.
Managing historical data.
There are several types of SQL Date functions but we are going to learn basics first.

The Three SQL functions we are going to learn in this lesson are –

  • DATEPART()
  • DATEADD()
  • DATEDIFF()

DATEPART()

This function returns an integer representing the specified datepart of the specified date.

SYNTAX:

DATEPART( datepart, date)

EXAMPLE:

The date is in YYYY-MM-DD format

SELECT DATEPART(MONTH, '2023-10-14') ;
SELECT DATEPART(YEAR, '2023-10-14') ;

You can also use abbreviations instead of the whole datepart. (check below)

To get any part of the data you can use the table below-

datepartAbbreviations
MONTHmm
YEARyy
DAYdd
WEEKww
WEEKDAYdw
DAYOFYEARdy

DATEADD()

It takes in 3 argument i.e. datepart, increment_int and date to return the new date.

DATEADD( datepart, increment_int, date)

EXAMPLE:

SELECT DATEADD(MONTH, 1, '2023-10-14');
SELECT DATEADD(DAY, 1, '2023-10-14');
SELECT DATEADD(YEAR, 1, '2023-10-14');
SELECT DATEADD(WEEK, 1, '2023-10-14');

Abbreviation can also be used in place of datepart argument.

DATEDIFF()

It is similar to using DATEADD()

SYNTAX:

DATEDIFF( interval, starting_DateTime, ending_DateTime)

EXAMPLES:

SELECT DATEDIFF(MONTH, '2023-10-14' , '2023-11-14');
SELECT DATEDIFF(DAY, '2023-10-14' , '2023-11-14');
SELECT DATEDIFF(WEEK, '2023-10-14' , '2023-11-14');
SELECT DATEDIFF(YEAR, '2023-10-14' , '2023-11-14');

The value is 0 because difference between two dates is a month not year. The function only returns integer as result.

Thanks For Reading!

Read more: Learn SQL Fast here