Tuesday, June 21, 2011

How to strip out the time of a DateTime and set it to what you want in SQL Server

Let's say I have two parameters @StartDate and @EndDate. I want to get all records with modifications between these DateTimes, but only the date part matters. If I don't know for sure what the Time part of the parameters are then i'm going to get bad results. So the best solution is to strip out the current time and add the time i want. In this case i want @StartDate's time to be 00:00:00 so it includes all records in that day, and @EndDate's time to be 23:59:59 so it includes all possible values in that day.

So here's how do that
SET @StartDate = DATEADD(dd, DATEDIFF(dd, 0, @StartDate),0)
SET @EndDate = DATEADD(dd, DATEDIFF(dd, 0, @EndDate),0) + '23:59:59'

The alternatives to this are not ideal. For example, you could use DATEPART and compare the year, month, and day of the record to the parameter. This means for every record you're executing DATEPART 3 times (i think it would cache the dateparting of the parameter, since it's on the right side, but I'm not entirely sure). That's going to be really slow compared to the solution above.

how it works
Start from the inside out. DATEDIFF(dd, 0, @StartDate) returns the total number of days since the minimum date ("0") in the system. So let's call this DAYS. The next part is DATEADD(dd, DAYS, 0). This is adding the number of days to the minimum date, thus stripping out the time.

No comments:

Post a Comment

There was an error in this gadget