Date and Time Functions

Below, you will find some of the more common SQL date and time functions that we will focus on in this class. For a more exhaustive list of these functions, you can refer to SQL: Date and Time Data Types and Functions .

Date and Time Functions

GETDATE

Returns a timestamp that includes the date and time of the server.

SELECT GETDATE();

2022-01-04 22:12:25.567

SYSDATETIME

Returns a timestamp that includes the date and time of the server. SYSDATETIME is more precise with the seconds than GETTIME.

SELECT SYSDATETIME();

2022-01-04 22:12:25.5675908

DATEADD

Adds a time period to a date. In the below example, we are increasing the month value from 02 (February) to 03 (March).

SELECT DATEADD(month, 1, '20220224');

2022-03-24 00:00:00.000

DATEDIFF

DATEDIFF calculates and returns the difference of a date part between an end date and a start date. It can also be used to compare months and years.

-- calculate and return the difference of a date part between end date and start date
SELECT DATEDIFF(day, '2022-11-13', '2022-12-12');

-- compare months
SELECT DATEDIFF(month, '2022-11-13', '2022-12-12');

-- compare years
SELECT DATEDIFF(year, '1984-11-13', '2022-12-12');

-- calculate and return difference
29
-- compare months
1
-- compare years
38

DATENAME

Returns a string representing the desired date part by selecting either a day, month, or year.

-- day
SELECT DATENAME(day, '2022-07-25');

-- month
SELECT DATENAME(month, '2022-07-25');

-- year
SELECT DATENAME(year, '2022-07-25');

-- day
25
-- month
July
-- year
2022

DATEPART

DATEPART returns an integer representing the desired date part by selecting either a day, month, or year.

-- day
SELECT DATEPART(day, '2022-08-21');

-- month
SELECT DATEPART(month, '2022-08-21');

-- year
SELECT DATEPART(year, '2022-08-21');

-- day
21
-- month
08
-- year
2022

DAY, MONTH, YEAR

You are also able to simply select a specific DAY, MONTH, or YEAR, returning an integer based value.

-- SELECT DAY
SELECT DAY('2022-08-21');

-- SELECT MONTH
SELECT MONTH('2022-08-21');

-- SELECT YEAR
SELECT YEAR('2022-08-21');

-- day
21
-- month
8
-- year
2022

CONVERT

You can convert other data types into dates and times using CONVERT. The example below pairs CONVERT with GETDATE.

SELECT CONVERT(varchar, GETDATE());

Jan 5 2022 7:25PM

FORMAT

FORMAT is used to set the format or reformat dates. The below examples are using the following date information: Jan 5, 2022 7:38PM.

-- requesting the format of day, month, year. Since the time is not requested, it is not returned.
SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');

-- Sends request for hour:minutes
SELECT FORMAT(GETDATE(), 'hh:mm');

-- dd/MM/yyyy
05/01/2022

-- hh:mm
07:38

Check Your Understanding