Dates

Anonymous contributor

Published Aug 4, 2021Updated Oct 19, 2021

Dates in SQL are often written in the following format:

DATE: YYYY-MM-DD
DATETIME: YYYY-MM-DD hh:mm:ss

We can use SQL’s date functions to transform data into a desired format. Since date functions can be database specific, verify the functions that exist on your relational database management system.

For example, suppose there’s a bodega table with the following data:

order_id	item	price	quantity	order_date
1	Donut	2.49	2	2022-08-16 08:04:23
2	Cookie	0.99	3	2022-08-16 09:43:00
3	Donut	2.49	1	2022-08-16 11:25:12
4	Egg Sandwich	7.99	1	2022-08-17 11:45:41
5	Ice Coffee	3.99	2	2022-08-17 12:18:50

SELECT TIME(order_date)
FROM bodega;

This would return just the time from the order_date column.

TIME(order_date)
08:04:23
09:43:00
11:25:12
11:45:41
12:18:50

Dates

DATE(): Converts timestamps to dates (YYYY-MM-DD).
DATEADD(): Returns a date/time interval added to a specified date. Versions are available in SQL Server and MySQL.
DATEDIFF(): Calculates and returns the difference between two date values. Available in SQL Server and MySQL.
DATETIME(): Returns the both the date and time (YYYY-MM-DD hh:mm:ss).
DATE_TRUNC(): Function to truncate a date, time, or timestamp to a specified interval (e.g. day or week). Available in PostgreSQL and SQL Server.
STRFTIME(): Returns a formatted date.
TIME(): Converts timestamps to time (hh:mm:ss).