20.2. SQL Part 2 - Date and Time Functions

20.2.1. Date and Time Data Types and Functions

Function	Definition	Syntax	Value Returned
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 this 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.	Calculates and returns the difference of a date part between an end date and a start date.	SELECT DATEDIFF(day, '2022-11-13', '2022-12-12');	29
	Can also be used to compare months.	SELECT DATEDIFF(month, '2022-11-13', '2022-12-12');	1
	Can also compare years.	SELECT DATEDIFF(year, '1984-11-13', '2022-12-12');	38
DATENAME.	Returns a string representing the desired date part.	SELECT DATENAME(day, '2022-07-25');	25
		SELECT DATENAME(month, '2022-07-25');	July
		SELECT DATENAME(year, '2022-07-25');	2022
DATEPART.	Returns an integer representing the desired date part.	SELECT DATEPART(day, '2022-08-21');	21
		SELECT DATEPART(month, '2022-08-21');	08
		SELECT DATEPART(year, '2022-08-21');	2022
DAY, MONTH, YEAR.	Each part of a date.	SELECT DAY('2022-08-21');	21
		SELECT MONTH('2022-08-21');	8
		SELECT YEAR('2022-08-21');	2022
CONVERT.	Paired with GETDATE to convert other data types into dates and times based on the desired format.	SELECT CONVERT(varchar, GETDATE());	Jan 5 2022 7:25PM
	These examples convert the server date into various formats. The first example is the default format, the second example is format number 7.	SELECT CONVERT(varchar, GETDATE(), 7);	Jan 05, 22
FORMAT.	Used to set the format or reformat dates. This example is using the following date information: Jan  5, 2022  7:38PM. The first example the server date is formatted date, month, year. The time is not requested, so it is not returned.	SELECT FORMAT(GETDATE(), 'dd/MM/yyyy');	05/01/2022
	The second example requests only the time in hours and minutes. Note that MM is used for months and mm is used for minutes.	SELECT FORMAT(GETDATE(), 'hh:mm');	07:38

20.2.1.1. Check Your Understanding

Question

Willow has a column in her table containing dates the library branches opened. She wants to compare them to today’s date. Which function would allow her to do that?

1. DATEADD

2. MONTH

3. CONVERT

4. DATEDIFF

Question

Willow has a table that contains dates library books are checked out. She uses the following function in her query: WHERE DATEPART(MONTH, BorrowDate) = 05. What will this return?

1. Rows of data where books were borrowed in the month of May

2. Rows of data where books were borrowed on the 5th day of the month.

3. Rows of data where books were borrowed in 2005.

4. Rows of data where the same library user borrowed exactly 5 books.

Question

Willow wants to have all the dates in her database in the same format of month-day-year. She used the following syntax: FORMAT(ModifiedDate, 'mm/dd/yyyy') and received an error message. Why?