How to compare column values to declared variable

Question

I was asked this interview question.

--Without modifying the following code:

DECLARE @StartDateInput SMALLDATETIME = '1/1/2018',
            @EndDateInput SMALLDATETIME = '1/1/2018'

--Modify the following query so that it will return contacts modified at any time on January 1st, 2018
SELECT *
FROM   dbo.Contacts

I tried the following query but this was not correct. I'm sure that I'm supposed to use the @EndDateInput variable as well but I wasn't sure how to use it. I don't think that this is the right way to approach this in general either.

SELECT *
FROM dbo.Contacts
WHERE  ModifiedDate = SMALLDATETIME

My first question to them would have been "what is the datatype of the ModifiedDate column?" (I suspect from the wording it's a time-bearing type, such as DATETIME) — Caius Jard, Nov 07 '20 at 09:15

score 2 · Accepted Answer · answered Nov 07 '20 at 09:19

It looks like the question is probing your understanding of date and datetime types, namely that a date with a time is after a date without a time (if there is even such a thing; most timeless dates are considered to be midnight on the relevant date, which is a time too.. in the same way that 1.0 is the same thing as 1, and 1.1 is after 1.0)

I'd use a range:

SELECT *
FROM   dbo.Contacts
WHERE ModifiedDate >= @StartDateInput AND ModifiedDate < DATEADD(DAY, 1, @EndDateInput)

Why?

This caters for datetimes that have a time component.
It doesn't modify the row data (always a bad idea, e.g. to cast a million datetimes to a date just to strip the time off, every time you query - precludes using an index on the column and is a massive waste of resources) just to perform the query.
It converts the apparent "end date is inclusive" implied by both @variables being the same, to a form that allows the exclusive behavior of < to work inclusively (adds a day and then gets rows less than the following day, thereby including 23:59:59.999999 ...)

The only thing I would say is that strictly, the spec only calls for one day's records, which means it's not mandatory to use the @EndDateInput at all. It seems logical to use it, but it could be argued that if the spec is that this query will only ever return one day, the @End variable could be discarded and a DATEADD performed on the @Start instead

`Cast( DateTimeColumn as Date )` is recognized by the query optimizer as a special case and can use an index seek, one of the exceptions to the rule that applying a function to a column in a `where` or `on` clause precludes using an index seek. [This](https://stackoverflow.com/questions/24659476/castdatetime-as-date-over-where-clause) answer explores some of the options. That said, I prefer the clarity of a [half-open interval](https://en.wikipedia.org/wiki/Interval_(mathematics)#Terminology) as shown in your code. — HABO, Nov 07 '20 at 15:59

score 2 · Answer 2 · answered Nov 07 '20 at 09:19

It is saying "any time" meaning consider the time component. With T-SQL the only reliable way is to use >= and < range query (exclusive upper range):

SELECT *
FROM dbo.Contacts
WHERE  ModifiedDate >= @StartDateInput and 
       ModifiedDate < dateadd(d, 1, @EndDateInput);

PS: Initial declaration of @StartDateInput and @ENdDateInput is not robust and probably by chance pointing to Jan 1st, 2018. If it were '1/2/2018' then it would be ambiguous between Jan 2nd and Feb 1st. Better use ODBC canonical and\or ISO 8601 strings like '20180101'.

How to compare column values to declared variable

2 Answers2