Dates

Anonymous contributor's avatar
Anonymous contributor
Anonymous contributor's avatar
Anonymous contributor
Published Aug 4, 2021Updated Oct 19, 2021
Contribute to Docs

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).
STRFTIME()
Returns a formatted date.
TIME()
Converts timestamps to time (hh:mm:ss).

All contributors

Looking to contribute?

Learn SQL on Codecademy