December 30, 2009 at 3:35 am
Run this query and 0 records are returned.
SELECT rqEmps.Employee_Name,
rqStores.StoreName,
rqStores.StoreID,
rqPunchClock.TimeIn,
rqPunchClock.EmployeeVerified,
rqPunchClock.ManagerVerified
FROM dbo.iQplanner_HR_PunchClock AS rqPunchClock
JOIN dbo.iQclerk_Stores AS rqStores ON rqPunchClock.StoreID = RQStores.StoreID
JOIN dbo.iQmetrix_Employees AS rqEmps ON rqPunchClock.EmployeeID = rqEmps.Id_Number
WHERE dbo.DateOnly2(rqPunchClock.TimeIn) BETWEEN '12/27/2009' AND '01/09/2010'
Run this query and thousands of records are returned.
SELECT rqEmps.Employee_Name,
rqStores.StoreName,
rqStores.StoreID,
rqPunchClock.TimeIn,
rqPunchClock.EmployeeVerified,
rqPunchClock.ManagerVerified
FROM dbo.iQplanner_HR_PunchClock AS rqPunchClock
JOIN dbo.iQclerk_Stores AS rqStores ON rqPunchClock.StoreID = RQStores.StoreID
JOIN dbo.iQmetrix_Employees AS rqEmps ON rqPunchClock.EmployeeID = rqEmps.Id_Number
WHERE dbo.DateOnly2(rqPunchClock.TimeIn) BETWEEN '12/13/2009' AND '12/26/2009'
BTW...dbo.DateOnly2 simply returns a varchar with the date only.
Obviously it has something to do with spanning 2 different years...just don't understand why.
Anybody have any ideas? Searched around and I'm at a lose.
TIA
Jason
December 30, 2009 at 4:19 am
This is because your function is returning a string instead of datetime (or smalldatetime). You should return a datetime datatype and compare it to your dates.
One more thing to note – the date ‘01/09/2010’ can be understood as January 9th, 2010 or as September 1th, 2010. The way that it will be understood is depended on how the server is configured, how the user that runs the query is configured and how the session is configured. This means that you can’t know for sure that the date will be understood the way that you meant it to be understood. You should use a format that will always be understood the same way regardless of the configuration on the server, user of session level. One format that can be used is a string with the structure of yyyymmdd. Fallowing your example it should be ‘20100109’ (assuming that you meant January 9th).
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 30, 2009 at 4:45 am
Remove that function immediately.
Once you get a nicely loaded database performance will be severely impared.
SQL Server has no option but to evaluate the function for each row REGARDLESS of any indexing.
You should do "Where rqPunchClock.TimeIn > @DateFrom and rqPunchClock.TimeIn < @DateTo +1 Day"
Assuming that DateTo is the midnight time (00:00:00)
December 30, 2009 at 9:12 am
Thank you gentlemen!
Not the answer I wanted at all...but the answer I'll have to live with.
One question for Dave...Is there any benefit to breaking the between logic into 2 different statements?
"Where rqPunchClock.TimeIn > @DateFrom and rqPunchClock.TimeIn < @DateTo +1 Day"
Will this work just as well?
"Where rqPunchClock.TimeIn BETWEEN @DateFrom AND @DateTo +1 Day"
Thanks again.
Jason
December 30, 2009 at 9:20 am
To answer for Dave, yes... there are a couple of reasons but the big one is "accuracy". Using BETWEEN includes both end points and if time is ever induced into the dates, you will miss nearly a whole day of infomation because a "whole date" or "date only" entry still has a time of midnight.
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply