SQL Date Trunc Examples
Date trunc commands are used to "round" a date to its nearest minute, hour, day, month, year, etc. It is frequently used with the GROUP BY command.
PostgreSQL
MySQL
SQL Server
Oracle
SELECT
id,
DATE_TRUNC('month', payment_date) AS month,
dollar_amount
FROM
rent_payments;
Notice how the payment_date column is rounded to the nearest month in the query result.
rent_payments table
| id | payment_date | dollar_amount |
|---|---|---|
| 1 | 3/1/2018 | 2000 |
| 2 | 3/2/2018 | 1500 |
| 3 | 3/1/2018 | 1800 |
| 4 | 3/3/2018 | 1900 |
| 5 | 4/2/2018 | 2000 |
| 6 | 4/2/2018 | 1900 |
Query result
| id | payment_date | dollar_amount |
|---|---|---|
| 1 | 3/1/2018 | 2000 |
| 2 | 3/1/2018 | 1500 |
| 3 | 3/1/2018 | 1800 |
| 4 | 3/1/2018 | 1900 |
| 5 | 4/1/2018 | 2000 |
| 6 | 4/1/2018 | 1900 |