How Date format working in the SQL

1

Hi team,

I have a table with 3 columns, ProductID, ValidFrom, and ValidTo.

Table:
ProductID ValidFrom ValidTo

TS154 1/1/2022 22:00 NULL
TS256 9/22/2019 22:00 12/31/2021 0:00

Below is our Query:

select ProductID From ProductList_C with (NOLock) where ValidFrom <= {d, '2022-01-01'} AND ValidTo IS NULL

The Query does not return the Product ID " TS154", If I use the full format with date and time its returns correctly, So I need to understand the "d" how the system is considered while running a Select Query.

Thanks

2 
SELECT ProductID
FROM ProductList
WHERE ValidTo IS NULL
	-- This will work regardless of the date/datetime type
	AND ValidFrom >= '20220101' -- 2022-01-01 00:00:00
	AND ValidFrom < '20220102'; -- 2022-01-02 00:00:00
3

Thanks

4

A little clarification - the form {d, '2022-01-01'} is for ODBC connections and isn't generally needed. For your specific issue, using that for your criteria resolves to the datetime '2022-01-01 00:00:00.000' and the datetime in the table is '2022-01-01 22:00:00.000'.

The check therefore becomes '2022-01-01 22:00:00.000' <= '2022-01-01 00:00:00.000' and that is false so the row is not returned.