You have a table named 'ORDERS' with a column 'ORDER DATE of data type DATE. You need to write a SQL query to retrieve all orders placed in the month of January 2023. Which of the following queries is

Question

Options

• ASELECT FROM ORDERS WHERE = 1 AND = 2023;
• B`sql SELECT FROM ORDERS WHERE ORDER DATE BETWEEN '2023-01-01' AND '2023-01-
• CSELECT FROM ORDERS WHERE 'YYYY-MM') = '2023-01';
• D`sql SELECT FROM ORDERS WHERE DATE PART(month, ORDER DATE) = 1 AND DATE
• ESELECT FROM ORDERS WHERE ORDER_DATE LIKE '2023-01%'

• A
  16% (3)
• B
  42% (8)
• C
  5% (1)
• D
  5% (1)
• E
  32% (6)

Explanation

Option B (WHERE ORDER_DATE BETWEEN '2023-01-01' AND '2023-01-31') is correct because it applies a range predicate directly on the raw column, allowing Snowflake's micro-partition pruning to skip entire data partitions that fall outside January 2023 - this is the core of Snowflake's query optimization engine.

Why the distractors fail:

• A - Malformed syntax; even if interpreted as MONTH() and YEAR() function calls, wrapping a column in a function prevents micro-partition pruning.
• C - Converting ORDER_DATE to a string with TO_CHAR(..., 'YYYY-MM') forces a full scan since Snowflake can no longer use partition metadata to skip data.
• D - DATE_PART(month, ORDER_DATE) similarly wraps the column in a function, defeating partition pruning even though the logic is correct.
• E - LIKE is a string operator; applying it to a DATE column either fails or requires an implicit cast, and even then would force a full scan.

Memory tip: In Snowflake, the rule is "bare column = fast." Whenever you apply a function to a column in a WHERE clause (DATE_PART, TO_CHAR, YEAR, MONTH, etc.), you break partition pruning. Prefer range filters (BETWEEN, >= / <) directly against date literals - they keep the column "bare" and let Snowflake's metadata do the heavy lifting.

No community discussion yet for this question.