Extract the Year — DATE_TRUNC and EXTRACT in SQL

The problem

Brightlane's compliance audit pulls the filing year from each contract's effective date.

Write a query to return the year component of the date '2024-03-15'.

Output:

A single row with one column, filing_year, containing the year as a number.

Schema · ecommerce 5 tables

categories

id integer

name text

parent_id? integer

products

id integer

name text

category_id integer

price numeric

stock_qty integer

attributes? jsonb

order_items

id integer

order_id integer

product_id integer

quantity integer

unit_price numeric

customers

id integer

name text

email text

city? text

country text

created_at timestamptz

is_active boolean

orders

id integer

customer_id integer

ordered_at timestamptz

status text

total_amount numeric

Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.

Worked solution Try it yourself first

Solution query

SELECT
  EXTRACT(
    YEAR
    FROM
      '2024-03-15'::date
  ) AS filing_year

The shape

EXTRACT(year FROM ...) pulls one numeric component out of a datetime and returns it as a number. Asking for the year of '2024-03-15' returns 2024, which is the filing year the compliance audit needs.

Clause by clause

SELECT EXTRACT(year FROM '2024-03-15'::date) evaluates the extraction against a single literal. The ::date cast resolves the string as a calendar date with no time-of-day component, which is the right type for a contract effective date. EXTRACT accepts date, timestamp, and timestamptz inputs equally; the field name is what decides which component comes back.
AS filing_year labels the output column as a domain quantity. There is no FROM because the value is a literal.

The trap

The return type is double precision, not integer. EXTRACT always returns a floating-point number regardless of which field is requested, so the year comes back as 2024 but its declared type is the same as it would be for EXTRACT(epoch FROM ...). The numeric value displays cleanly here because the year has no fractional part, but any downstream code that pattern-matches on the column type will see a float, not an integer.

You practiced EXTRACT(year FROM ...) — pull a single numeric component out of a datetime, returned as a number rather than a date.

Return the year component of the date `'2024-03-15'`

The shape

Clause by clause

The trap

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.