SQL

Extract Parts and Manipulate Dates in SQL

Master SQL date functions to extract year, month, day, truncate dates, and add/subtract intervals. Essential for dynamic reporting, analysis, and scheduling.

SELECT
    order_date,
    EXTRACT(YEAR FROM order_date) AS order_year,
    EXTRACT(MONTH FROM order_date) AS order_month,
    DATE_TRUNC('month', order_date) AS start_of_month,
    order_date + INTERVAL '7 days' AS delivery_date_estimate
FROM
    orders
WHERE
    order_date BETWEEN '2023-01-01' AND '2023-12-31';

How it works: This query showcases several common SQL date manipulation functions. `EXTRACT(YEAR FROM ...)` and `EXTRACT(MONTH FROM ...)` retrieve specific components of a date. `DATE_TRUNC('month', ...)` truncates a date to the beginning of its month (e.g., '2023-01-15' becomes '2023-01-01'). `order_date + INTERVAL '7 days'` adds a specified time interval to a date, which is useful for calculating future dates. The `WHERE` clause filters records within a defined date range.

Extract Parts and Manipulate Dates in SQL

Need help integrating this into your project?