Using DATEPART for AM works correctly but does not work for PM query

Question

I'm using a query to pull a report for our 3rd shift folks.

The issue I just noticed when the report was mailed with 0 results is that I have it set right now to run from 6pm to 5:59am the next morning. Since I have the date field = the GETDATE, it doesn't see the results from the night prior(1800-2359). I was thinking of having this just pull from like -12 hours, but the button can be pressed at anytime to look at results.

So if they pushed it before the end of the shift, it would show some of 1st shifts entries. I want it to only run from 1800-0559 the next morning, no matter if the button is pressed before midnight or after midnight. I hope this makes sense. I'm not even sure if the between recognizes to go to 00 after it's hit 2359. I have this built into a VB.net application, and they can press the button anytime they want to get results. This is for management to track what is being done as the employees are required to enter data real time when issues are taking place.

Thanks for any help. Hope I made enough sense! :D

SELECT 
   Assignment, Datemodified, General, 
   IncNumber, NextSteps, PDCRStatus, 
   RootCause, Status, Summary, 
   Timings, UserID 
FROM 
   Turnover 
WHERE DATEPART(HOUR, datemodified) between 18 and 05 
AND CONVERT(NVARCHAR(50),datemodified,103) = CONVERT(NVARCHAR(50),GETDATE(),103);

How can a number be greater than (or equal to) 18 and less than (or equal to) 5? — , Nov 07 '12 at 13:05

score 0 · Answer 1 · answered Nov 07 '12 at 13:12

0

I guess when you are checking for a particular time range, the date is changing over the time i.e., 1800 hrs of a day to 0500 hrs of the next. I see that your query is checking for only the time, but date is not considered. I guess if you can check for the date as well as time together, it should work. As of now I do not have sql server on my machine, else would have helped you with a sample query.

Cheers, Hope it helps!

answered Nov 07 '12 at 13:12

Uma

11
2

Well date is considered with the = GETDATE, but if my query is ran after 2359, it would give any results because it's looking at that date only, not the 6 hours from the date prior. – user1800374 Nov 07 '12 at 16:55

score 0 · Answer 2 · answered Dec 20 '13 at 10:52

i'm assuming you want all the data starting from yesterday 18:00.

SELECT 
   Assignment, Datemodified, General, 
   IncNumber, NextSteps, PDCRStatus, 
   RootCause, Status, Summary, 
   Timings, UserID 
FROM 
   Turnover 
WHERE datemodified >= dateadd(HH,-6,convert(datetime,convert(date,GETDATE())))

i'm getting the current datetime with getdate and extract the day. when converting it back to a datetime i have 00:00, so i subtract 6 hours with dateadd and hence select yesterday 18:00. however i do not know how this is supposed to behave at certain hours. it will always go to yesterday 18:00, which means when you start it at 23:59, you will see an interval of 29 hours 59 minutes. when started at 00:00, you will see 6 hours.

Using DATEPART for AM works correctly but does not work for PM query

2 Answers2