Selecting Records Modified on a Specific Date Using SMALLDATETIME Variables

encountered an SQL challenge in an interview and need some help. The task was to modify a given query to return all contacts modified at any time on January 1st, 2018, using two SMALLDATETIME variables, @StartDateInput and @EndDateInput.

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


Query to modify

SELECT *
FROM   dbo.Contacts;

I attempted to modify the query but couldn't figure out how to incorporate @EndDateInput correctly. My attempt was:

SELECT *
FROM dbo.Contacts
WHERE ModifiedDate = SMALLDATETIME;

However, this approach seems incorrect. I think the solution should involve both @StartDateInput and @EndDateInput to accurately capture contacts modified at any time on January 1st, 2018, but I'm unsure how to achieve this.

Could anyone suggest the correct way to modify this query to meet the specified criteria?

Solution

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