In SQL, which query correctly extracts only the year part from a date column for each row, for example from an order_date column?

Introduction / Context:
Extracting components such as year, month, or day from a date column is a very common requirement in reporting and data analysis. Interviewers often ask you to write a query that returns only the year part from a date column to see if you are comfortable with SQL date functions and aware of standard syntax. This question focuses on identifying the correct expression for extracting the year component in a portable SQL style.

Given Data / Assumptions:

- There is a table named Orders with a date or timestamp column called order_date.

- The goal is to return a derived column named order_year that contains only the year number for each row.

- We assume a SQL dialect that supports the EXTRACT function in the standard form.

- The query should not return the full date, only the year component.

Concept / Approach:
Many relational databases support the EXTRACT function, which allows you to retrieve a specific field from a date or timestamp. The general form is EXTRACT(field FROM source). To extract the year from order_date, you use EXTRACT(YEAR FROM order_date). The result is typically a numeric value representing the year, such as 2025. This is more precise and explicit than formatting the date as a string and then parsing it. Although some databases offer other functions, such as YEAR() in some dialects, EXTRACT is closer to the SQL standard and is widely recognized.

Step-by-Step Solution:
Step 1: Identify the operation: we want the year part of a date column. Step 2: Recall that the EXTRACT function can pull specific components from dates or timestamps. Step 3: Write the expression EXTRACT(YEAR FROM order_date) to produce the year value. Step 4: Wrap this expression in a SELECT statement, selecting from the Orders table. Step 5: Provide an alias order_year for readability, resulting in SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM Orders. Step 6: Compare with the options and select the one that matches this final query, which is option A.

Verification / Alternative check:
As a check, imagine running the query on a row where order_date is 2024-11-15. The EXTRACT(YEAR FROM order_date) expression should return 2024. The query in option A therefore returns a single column order_year containing values such as 2024, which is exactly what we want. Option C, which formats order_date as a complete date string, does not isolate the year and would return something like 2024-11-15. Option D returns the month, which would be 11 in this example. This confirms that only option A provides the desired year component.

Why Other Options Are Wrong:
Option B is syntactically incorrect because YEAR is not a field and the order of the FROM clause is wrong in most SQL dialects. Option C uses TO_CHAR to format the complete date as a string in the pattern year dash month dash day, which returns a full date string rather than only the year. Option D uses the MONTH function, which returns the month number, not the year. Therefore, none of these options meet the requirement of returning only the year part.

Common Pitfalls:
One common pitfall is to rely on string formatting functions like TO_CHAR or DATE_FORMAT instead of using date extraction functions, which can be less clear and less portable. Another issue is to forget to alias the derived column, which makes result sets harder to read in reports. In interviews, it is good practice to mention that EXTRACT is a standard approach, and that while some databases offer alternative functions, the core idea is to isolate the year field from the date using a dedicated date function rather than manual string manipulation.

Final Answer:
The correct SQL query is SELECT EXTRACT(YEAR FROM order_date) AS order_year FROM Orders.