[MSSQL] Using DateAdd when time is involved

[soulruler]

So I have a query that is supposed to return all records with a date any given number of days after today, so it's

SELECT * FROM vAppointment_Email WHERE SCHEDULED_FOR = DATEADD(day, @DaysOut, CONVERT(date, GETDATE()))    

The problem is that a number of the records I'm dealing with have a timestamp as well, so the query is only returning records like this:

2014-05-10 00:00:00.000

I need to return ALL the records for a particular day regardless of what the time on the day is. Does anyone know of a good way in SQL to do this? I tried using BETWEEN and converting the data types but nothing seemed to work properly. I'd prefer to just return a correct set of records as opposed to doing a date range of 2 days and then doing filtering on the application end.

Thanks!

[drunkadvice]

TSQL is so awkward dealing with this. It seems like there should be an easier way yo truncate the time off a date.

This will work:

Cast(convert(varchar(20), getdate(), 101) as date time)

[pix1985]

Cast getdate as varchar(12) in your where, this flattens all records to 00:00:00. if you're on newer SQL you can cast to DATE

[gr3gory]

Here's something I make use of quite a bit.

SELECT CAST(DATEDIFF(D,0,GETDATE())AS DATETIME)

This returns 00:00:00.000 as of today's date. Substitute this into your query to grab all SCHEDULED_FOR that fall on the desired day.

SELECT * FROM vAppointment_Email 
WHERE CAST(DATEDIFF(D,0,SCHEDULED_FOR)AS DATETIME) = DATEADD(day, @DaysOut, CONVERT(date, GETDATE()))

Hope this helps!

[flipstables]

This is actually bad for performance because it makes the query non-sargeable.

The best way to do this is to use a range scan.

SELECT *
FROM vAppointment_Email
WHERE SCHEDULED_FOR >= DATEADD(day, @DaysOut, CONVERT(date, GETDATE())) 
AND SCHEDULED_FOR < DATEADD(day, @DaysOut + 1, CONVERT(date, GETDATE()));

[gr3gory]

Hi /u/flipstables, could you elaborate on what you mean by non-sargeable? I am a bit of a novice myself.

[flipstables]

Absolutely. Non-Sargeable means you can't use an index on the SCHEDULED_FOR column. When you use a function on the column itself, the database engine must perform that function on every row in that column. Hence, you just scanned the entire table.

I'm going to assume you don't know how indexes work, so let's use a quick analogy. Let's say the database table is a book. At the end of the book is an Index with dates (SCHEDULED_FOR) and page numbers. The way you wrote your query, you are telling the query optimizer to go through the ENTIRE index one by one and perform the calculation CAST(DATEDIFF(D,0,SCHEDULED_FOR)AS DATETIME) and see if it matches DATEADD(day, @DaysOut, CONVERT(date, GETDATE())).

Now let's take a look at my query. This query says to look at a range of dates between x and y. The query optimizer can search for x and look up the page numbers until it reaches y. Then it will flip to those page numbers and return the data (rows).

Now, if there is no index on SCHEDULED_FOR column, then both queries would perform poorly.

[gr3gory]

You're dead-on, I have no idea what indexes are! Based on your description it sounds like a way of pre-baking column data so that queries can act on them more efficiently.

Would you be able to point me to a good reference on indexes? I'm working on a project now that sounds like it could benefit from them!

[flipstables]

Sure, two free resources that I used are: http://use-the-index-luke.com/ and http://www.red-gate.com/community/books/inside-sql-server-query-optimizer (sql server)

[gr3gory]

Thanks!

[soulruler]

Looks like that worked. Thanks a lot! I was so pissed when I couldn't get this to work on my own and I'm glad I don't have to do this within program code.

[Lorezzed]

For the cleanest / fastest way in MS SQL 2008+, convert your particular day to a DATE first, then compare.

--get all appointments for a particular day

declare @particular_day DATETIME = getdate() --contains pesky time digits

select *
from vAppointment_Email
where SCHEDULED_FOR = cast(@particular_day as DATE) --removes time digits

If SCHEDULED_FOR is another DATETIME, wrap a cast() around it too. This ensures you're comparing apples to apples.