How do you derive a year column and a due date from order_date in the SELECT list?

## Two constructs do most of the work Date handling in a SELECT list usually comes down to two operations: pulling a component out of a date, and shifting a date by an amount of time. The standard has a spelling for each. **EXTRACT** takes a field name and a datetime source and returns a number: ```sql SELECT order_id, EXTRACT(YEAR FROM order_date) AS order_year, EXTRACT(MONTH FROM order_date) AS order_month FROM orders; ``` The field is a keyword, not a string: `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`. The result is numeric — `2026`, not `'2026'` and not a date — which matters if you then concatenate it (you will need a `CAST`) or sort by it. **Interval arithmetic** shifts a date: ```sql SELECT order_id, order_date + INTERVAL '30' DAY AS due_date FROM orders; ``` An interval literal is written `INTERVAL '<value>' <unit>`, and it participates in ordinary `+` and `-` expressions against dates and timestamps. **Current time** comes from the niladic functions `CURRENT_DATE`, `CURRENT_TIMESTAMP` and `LOCALTIMESTAMP` — written without parentheses in standard SQL. ## Types, not strings A frequent misconception is that dates are stored as formatted text and that these functions do string surgery. They do not. A date column holds a date value; `EXTRACT` reads a component of it, and interval arithmetic performs calendar arithmetic that respects month lengths and leap years — `DATE '2026-01-31' + INTERVAL '1' MONTH` is handled by calendar rules, not by adding 30 to a number. Similarly, a date literal is written with its type keyword: `DATE '2026-03-14'`, `TIMESTAMP '2026-03-14 09:30:00'`. Comparing a date column to a bare quoted string relies on implicit conversion and on the session's date format, which is a portability and correctness hazard. ## The portability picture This is the least portable area of everyday SQL, and it is worth knowing the shape of the divergence rather than memorising every function: - `EXTRACT` is implemented by PostgreSQL, MySQL and Oracle. **SQL Server does not have it**; the T-SQL equivalents are `DATEPART(year, order_date)` and the shortcut `YEAR(order_date)`. SQLite has neither and uses `strftime('%Y', order_date)`. - Standard interval literals (`INTERVAL '30' DAY`) work in PostgreSQL and Oracle; MySQL writes the amount unquoted (`INTERVAL 30 DAY`) and also offers `DATE_ADD(order_date, INTERVAL 30 DAY)`; SQL Server has no interval literal at all and uses `DATEADD(day, 30, order_date)`. - There is **no portable date-difference function**. PostgreSQL subtracts dates directly; MySQL and SQL Server both have a `DATEDIFF`, with different argument orders and different unit semantics. Do not assume the one you know behaves the same elsewhere. Because of this, the practical advice is not "memorise four dialects" but "know that date maths is a dialect boundary": keep it in as few places as possible, and when a query must be portable, prefer `EXTRACT` and interval literals and confirm them on the target engine. ## Time zones and truncation Two more things to keep in mind when deriving date columns. If the source is a timestamp with time zone, `EXTRACT(HOUR FROM ...)` gives you the hour in whatever zone the value is rendered in, so "orders per hour" quietly depends on session settings unless you convert explicitly. And grouping by a derived period — a month bucket, a week bucket — needs a deliberate truncation expression; the spellings for that (`DATE_TRUNC`, `TRUNC`, `DATEFROMPARTS`, formatting functions) are engine-specific, so pick one and centralise it. ## A note on where the expression lives Deriving a date part in the SELECT list is fine — it is presentation. Putting the same function call on a column inside a `WHERE` predicate is a different matter with its own consequences for how the engine can find rows, and it is usually rewritten as a range comparison instead. Keep that distinction in mind: the same expression is harmless in the output list and worth scrutinising in a filter.