You have a Fabric workspace. You are debugging a statement and discover the following issues: Sometimes, the statement fails to return all the expected rows. The PurchaseDate output column is NOT in…

This question tests NULL-safe SQL filtering and T-SQL date formatting in Microsoft Fabric (Synapse/Lakehouse SQL). The two bugs map to two distinct T-SQL concepts: ANSI NULL comparison behavior causing missing rows, and the FORMAT() vs CONVERT() choice for date display.

Ingest and transform data

Question

You have a Fabric workspace.
You are debugging a statement and discover the following issues:
Sometimes, the statement fails to return all the expected rows.
The PurchaseDate output column is NOT in the expected format of mmm dd, yy.

You need to resolve the issues. The solution must ensure that the data types of the results are retained. The results can contain blank cells.
How should you complete the statement? To answer, select the appropriate options in the answer area.
SELECT
item_id as ItemId
-- DROPDOWN 1 for ItemName
,item_description as ItemDescription
-- DROPDOWN 2 for PurchaseDate
FROM
Table1
WHERE
item_type = @itemtype_parameter

Explanation

Approach. DROPDOWN 1 (ItemName) should use the plain column reference - e.g., ',item_name AS ItemName' - with no ISNULL/COALESCE wrapping, because the requirement explicitly allows blank cells (NULLs) and wrapping would change the effective data type of NULL to an empty string, violating 'data types of results are retained'. DROPDOWN 2 (PurchaseDate) should use FORMAT(purchase_date, 'MMM dd, yy') AS PurchaseDate - FORMAT is the only built-in T-SQL function that produces the 'mmm dd, yy' output pattern (e.g., 'Jan 01, 26'); CONVERT style codes cannot produce this exact pattern. The missing-rows bug is caused by the ANSI-NULL rule: 'column = NULL' always evaluates to UNKNOWN, so any row where item_type IS NULL is silently excluded; the WHERE clause should be written as 'WHERE item_type = @itemtype_parameter OR (item_type IS NULL AND @itemtype_parameter IS NULL)' to surface those rows as blank cells rather than dropping them.

Concept tested. ANSI NULL semantics in WHERE equality predicates (causing silent row loss) combined with T-SQL date formatting - FORMAT() vs CONVERT() style codes - and the rule that using ISNULL/COALESCE to suppress NULLs in the SELECT list both violates a 'retain data types' constraint and prevents blank cells from appearing in output.

Reference. Microsoft Fabric / Azure Synapse Analytics T-SQL docs: FORMAT (Transact-SQL), CONVERT date styles, NULL handling and ANSI_NULLS behavior

Topics

#SQL#Data Transformation#Date Formatting#NULL Handling

Community Discussion

No community discussion yet for this question.

Full DP-700 Practice