Select Based on actual time
-
To T-sql experts here is what I have as an issue...
SELECT UniqueId,
Name,
Client,
completiondate FROM ActualTime
WHERE
if current time is 10 o clock then get all records that where completed between yesterday and this morning until 10
if current time is 2 pm then get all records that where completed between 10 am to 2 pm today..
I am not sure how the syntax would be please help...
-
Hi,
In your where clause use two variables, eg @start_time and @end_time, so your where clause will look something like
"where all records completed between @start_time and @end_time"
Then at the beginning of the query use an if statement with datepart(hh,getdate()) (this will return the current hour) to set the values of @start_time and @end_time
e.g.
declare @start_time int
declare @end_time int
declare @current_hour int
select @current_hour = datepart(hh,getdate())
if @current_hour = 16
begin
select @start_time = 8
select @end_time = 10
end . . . .
Hope this helps.
-
this does not work as the completiondate is a datetime field..
Also this query is a part of a scheduled task so it looks at the current system time..
-
where
completiondate >
CASE DATEPART(HH, getdate())
WHEN 10 then (Getdate() - 1) -- yesterday morning
When 14 then DateAdd(hh, -4, getdate()) -- 10AM today
End
NOTE: you specified exact times (equal to 10).
You may need to polish this. -
Yes thanks a lot!
I see what I was doing wrong..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply