Posted by Hugo Kornelis on 01/25/06 22:13

On 25 Jan 2006 10:03:53 -0800, Chris wrote:

>Hello,
>
>I am attempting to build a MS SQL query that will return data from
>"today"; today being current day 8:00AM-10:00PM today. My goal is to
>return the data from a table that is written to throughout the day, the
>query will provide the current grade of service in our call center.
>
>I am having difficulty defining my where clause:
>
>- I can accomplish my goal my statically defining my 'date between' as
>the actual date and time (not ideal)
>
>- I can accomplish the second part of my date using CURRENT_TIMESTAMP;
>but I am unable to define the starting point
>
>Here is where I am thus far:
>
>/* We are going to count the total calls into each queue from start of
>business today (8:00AM) to now */
>
>select COUNT(Result) as "Total Sales Calls Offered" from
>dbo.QueueEncounter
>where Direction='0'
>and
>QueueID='1631'
>and
>/* This is where I get lost */
>Time between DATEPART(day, GETDATE()) and DATEPART(day, GETDATE())
>
>Clearly the last line returns zero as there are no calls between the
>same date range. How can I add to that line, or write this to work?
>
>Any thoughts?
>
>Thanks for the help.
>
>-Chris

Hi Chris,

You say you want rows for today, 8:00AM-10:00PM. Does this imply that
the table also contains rows outside the 8:00AM-10:00PM time frame that
you don't want to include?

AND Time BETWEEN DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'8:00AM')
AND DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP),
'10:00PM')

Note that this will include a row with time exactly equal to 10 PM, but
exclude a row with time 3 milliseconds after 10PM.

If you want all rows for the whole day (0:00 - 24:00), use
AND Time >= DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 0)
AND Time < DATEADD(day, DATEDIFF(day, 0, CURRENT_TIMESTAMP), 1)

--
Hugo Kornelis, SQL Server MVP

[Back to original message]