SQL Date Compare using only Date not Time

Question

This was originally a question about how to implement this, because I was stuck in a certain part, but now I am curious why this happens in the first place. I only needed to compare the dates, not the time, which would not be a problem if the times were not different. The code below shows the request from which I initially tried to execute

SELECT * FROM Employee e inner join OT_Hours o on o.Emp_ID=e.Emp_ID inner join Position p on p.Position_ID=e.Position_ID inner join Signup_Sheet s on s.Employee_ID=e.Emp_ID WHERE e.Eligible_OT=1 and s.Day_Shift = 1 and p.Position_Name = 'Controller' and Convert(Varchar(20),s.Date,101) = '07/26/2010' and Convert(Varchar(20),o.Date,101) <='07/26/2010' and Convert(Varchar(20),o.Date,101) > '07/26/2009' and o.Quantity NOT IN(0.3) order by o.Date DESC 

I did not get the result when I ran this query, but when I deleted the second last row, it will return 12 results (<=), and when I delete the third last row, but saved the second, it will return 6 results (>). After reviewing the data, I could see that 4 of these results should have been returned. Now about the alien part. Below is the code I'm currently using.

 SELECT DISTINCT o.Date, e.Emp_ID as Emp_ID, e.First_Name+ ' ' +e.Last_Name as Name, o.Quantity as Sum FROM Employee e left join OT_Hours o on o.Emp_ID=e.Emp_ID left join Position p on p.Position_ID=e.Position_ID left join Signup_Sheet s on s.Employee_ID=e.Emp_ID WHERE e.Eligible_OT=1 and s.Day_Shift = 1 and p.Position_Name = 'Controller' and Convert(Varchar(20),s.Date,101) = '07/26/2010' and o.Date between '07/26/2009' and '07/26/2010' and o.Quantity NOT IN(0.3) order by o.Date DESC 

This query will return the results, but I also checked it, as I did another, when o.Date was above and below the specified date. When the date was <= 16, the results were returned when 8 results were returned. The final query returned 6 results. Now this is not the production database that I am accessing, and I use it only so that the data does not change. Any explanation of why this is happening? I suppose this has something to do with converting it to varchar, and it can't compare correctly, but that doesn't explain why I would get 12 <=, 6>, and then no results at the end. Also, if anyone knows a better way to implement this, please let me know.

Answer 1

In the third of the last line of your first query, you are comparing two lines.

As such, 01/02/2009 more than 01/01/2010

I usually make the date BETWEEN '01/02/2009 00:00:00.000' AND '01/01/2010 23:59:59.997' , but it will be interesting to see the best solution.

Answer 2

Two requests do not match - this:

 and o.Date between '07/26/2009' and '07/26/2010' 

... is equivalent to:

 and o.Date >= '07/26/2009' and o.Date <= '07/26/2010' 

BETWEEN is an ANSI standard and is included in every database I have ever come across.

Remember that if you do not specify a time part for DATETIME, the default value will start at midnight - 00:00:00.

Answer 3

I learned this technique from SQL Server Magazine in February 2007 (calculating by date in time of Itzik Ben-Gan). This way your “between” will work regardless of whether the line date is after midnight, since everything was normalized to be at midnight with this comparison:

 select * from someTable where dateadd(day, datediff(day, 0, somedate), 0) between '07/26/2009' and '07/26/2010' 

Dates and dates work together to turn off time and leave a date. You can then compare it with string literals or other dates that had the same modification. I would recommend including this in a function.

EDIT: Based on OMG Ponies comments. This will not use the index in the date column. An alternative would be to use a time-removal technique in addition to the technique mentioned above. Therefore, instead of doing this in a table column, do it on the last argument between. You may have a function like this:

 CREATE FUNCTION [dbo].[fn_enddate](@enddate datetime) RETURNS datetime AS BEGIN DECLARE @endOfDay datetime set @endOfDay = dateadd(millisecond, -2, dateadd(day, datediff(day, 0, @enddate) + 1, 0)) return @endOfDay END 

This takes the date of the argument, sets it to midnight the next day, and then subtracts two milliseconds, giving the end of the day for that day. So you can do:

 select * from someTable where somedate between '07/26/2009' and dbo.fn_enddate('07/26/2010') 

Answer 4

If your database is SQL Server, then what I did works very well to turn off time, it is something like the following ....

 SELECT DISTINCT o.Date, e.Emp_ID as Emp_ID, e.First_Name+ ' ' +e.Last_Name as Name, o.Quantity as Sum FROM Employee e left join OT_Hours o on o.Emp_ID=e.Emp_ID left join Position p on p.Position_ID=e.Position_ID left join Signup_Sheet s on s.Employee_ID=e.Emp_ID WHERE e.Eligible_OT=1 and s.Day_Shift = 1 and p.Position_Name = 'Controller' and CAST(FLOOR(CAST(s.Date AS FLOAT)) AS DATETIME) = '07/26/2010' and CAST(FLOOR(CAST(o.Date AS FLOAT)) AS DATETIME) between '07/26/2009' and '07/26/2010' and o.Quantity NOT IN(0.3) order by o.Date DESC 

Depending on how your parameters are set to '07 / 26/2010 ', '07 / 26/2009', you can save them in datetime variables and perform the same operation cast(floor(cast(@datevar as float)) as datetime) .

This seems to be a repost. Check the accepted answer here ... How to remove the temporary part of the datetime value (SQL Server)?

Answer 5

 ... AND s.Date BETWEEN '2010-07-26 00:00:00.000' AND '2010-07-26 23:59:59.997' AND o.Date BETWEEN '2009-07-26 00:00:00.000' AND '2010-07-26 23:59:59.997' ... 

Answer 6

My suggestion was to adjust your criteria to cover the time, not the dates themselves. If you convert or otherwise manipulate columns (columns) for comparison, you can destroy the use of indexes in the query.

Also always compare dates with dates. When you convert them to strings and try to compare, you run into problems, as Chris Diver points out.

In your case, I would try:

 SELECT o.Date, e.Emp_ID as Emp_ID, e.First_Name+ ' ' +e.Last_Name as Name, o.Quantity as Sum FROM Employee e LEFT JOIN OT_Hours o ON o.Emp_ID = e.Emp_ID LEFT JOIN Position p ON p.Position_ID = e.Position_ID LEFT JOIN Signup_Sheet s ON s.Employee_ID = e.Emp_ID WHERE e.Eligible_OT = 1 AND s.Day_Shift = 1 AND p.Position_Name = 'Controller' AND (s.Date >= @signup_date AND s.Date < DATEADD(dy, 1, @signup_date)) AND (o.Date >= @order_start_date AND o.Date < DATEADD(dy, 1, @order_end_date)) AND o.Quantity NOT IN (0.3) ORDER BY o.Date DESC 

You need to make sure that parameters or variables do not always have a time part.