Search code examples
sqlsql-servert-sqldatetimedate-comparison

sql date comparison

I was given a query similar to this

select * 
from stuff where stuff.id = 1 
and start_Dt < = todays_date 
and End_Dt > = todays_date

I asked the person who gave it to me why the date comparison, the answer was "The start and end dates are necessary to ensure a unique record match"

I'm confused, wouldn't that comparison equate to any date possible?

Solution

  • The reason is, that the IDs in your table are not unique, i.e. you can have more than one row with stuff.id = 1.
    But only one at any given time is active. This is checked with the date comparison: It returns the row with stuff.id = 1 that is currently valid. This is the row where the start date is in the past (start_Dt <= todays_date) and the end date is in the future (End_Dt >= todays_date).