Tutorial :SQL Date Compare by only using Date not Time



Question:

This was originally going to be a question about how to implement this because I was stuck at a certain part but I am now curious as to why this was happening in the first place. I needed to compare only the dates not the time which wouldn't have been a problem if the times didn't differ. The code below shows the query I was originally trying

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 would get no result when I ran that query, but when I removed the second last line it would return 12 results (<=) and when I removed the 3rd last line but kept the second last it would return 6 results (>). After reviewing the data I could see that 4 of those results should have been returned. Now for the wierd part. Below is the code I am 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 results but I also tested it like I did the other one when o.Date was above and below the date specified. When the date was <= 16 results were returned, when > 8 results were returned. The final query produced 6 results. Now this is not a production database I'm querying against and I'm the only one using it so the data did not change. Any explanation on why this was happening? I'm assuming it had something to do with converting it to varchar and it couldn't compare properly but that doesn't explain why I would get 12 <=, 6 > and then no results in the end. Also if anyone knows a better way to implement this please let me know.


Solution:1

In the third from last line in your first query you are comparing two strings.

As such 01/02/2009 is greater than 01/01/2010

I usually do date BETWEEN '01/02/2009 00:00:00.000' AND '01/01/2010 23:59:59.997' but it will be interesting to see a better solution.


Solution:2

The two queries aren't the same - this:

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

...is the equivalent of:

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

BETWEEN is ANSI standard, and inclusive on every database I've ever encountered.

Mind that if you don't specify a time portion for DATETIMEs, the value defaults to starting at midnight of the day - 00:00:00.


Solution:3

I learned this technique from SQL Server Magazine February 2007 (Datetime Calculations by Itzik Ben-Gan). This way, your 'between' will work regardless of whether the row's date is after midnight, as everything has been 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'   

The datediff and dateadd work together to strip off the time and leave the date. You can then compare it to string literals, or other dates that have had the same modification done. I'd recommend putting this in a function.

EDIT: Based on OMG Ponies' comments. This will not take advantage of an index on the date column. An alternative might be to use the time stripping technique in addition to the technique others have mentioned. So instead of doing it on the table's column, do it on the last argument to 'between'. You could have a function like so:

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 argument date, sets it to midnight the next day, then subtracts two milliseconds, giving the end of the day of the given datetime. So then you could do:

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


Solution:4

...  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'  ...  


Solution:5

My suggestion would be to adjust your criteria to cover times, not the dates themselves. If you convert or otherwise manipulate the column(s) for the compare you can destroy the use of indexes in the query.

Also, always compare dates to 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'll need to make sure that the parameters or variables are always without a time portion.


Solution:6

If your database is SQL Server what I have done that workS pretty well to strip off the time 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 '07/26/2010', '07/26/2009' you could store them in datetime variables and perform the same cast(floor(cast(@datevar as float)) as datetime) operation.

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


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »