SQL Dates
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-DDDATETIME: 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()
- Adds a specified time interval to a date and returns the modified date.
- DATEDIFF()
- Calculates the difference between two date or timestamp values and returns the result as an integer.
- 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).
Contribute to Docs
- Learn more about how to get involved.
- Edit this page on GitHub to fix an error or make an improvement.
- Submit feedback to let us know how we can improve Docs.
Learn SQL on Codecademy
- Learn to analyze data with SQL and prepare for technical interviews.
- Includes 9 Courses
- With Certificate
- Beginner Friendly.18 hours
- In this SQL course, you'll learn how to manage large datasets and analyze real data using the standard data management language.
- Beginner Friendly.5 hours