Dates

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

BETWEEN
Returns records where a specified value is within a given range, including the boundary values.
CURDATE()
Returns current date (YYYY-MM-DD).
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.
EXTRACT()
Used to extract a specific part from a given date such as year, month, etc.
STRFTIME()
Returns a formatted date.
TIME()
Converts timestamps to time (hh:mm:ss).

All contributors

Contribute to Docs

Learn SQL on Codecademy