CallType Summary Hourly Report

[obc8407gopal]

Hi James,

Thanks for the response

"

The simplest method would be to make a copy of a report such as "Call Type Historical All Fields Daily", and change the Interval calculation to:

Interval=DATEADD(hour, DATEDIFF(hour,0,CTHH.DateTime),0)

Make sure you change the same statement in the GROUP BY and ORDER BY statements as well."

James,

Iam trying to understand the function DATEDIFF(hh,0,'YYYY-MM-XX hh:mm:ss') corresponding to CallType Interval Report.

when I try to execute below query , I get and answer of 1004410

select

DATEDIFF(hour,0,'2014-08-01 10:15:00')

After that I did the DATEADD and I got the Date with Hour.

select DATEADD(hour, DATEDIFF(hour,0,'2014-08-01 10:15:00'),0)

result is 2014-08-01 10:00:00

Iam trying the understandand Value 0 in the Function.

Can you explain.

[Jameson Gagnepain]

Gopal,

DATEDIFF(hour,0,X) gives the number of hours between X and 0. 0, when converted to a SQL DATETIME value, is 1900-01-01 00:00:00.

The DATEADD function is used here to convert the number of hours back to a SQL DATETIME value. The combination of the two functions in this way is a quick and useful way to find the nearest hour (rounding down) for a given DateTime.

w3schools.com has good information on these functions:

Date Functions in SQL Server and MySQL

-Jameson

[richapar · Accepted solution]

I believe I can answer this question for you.

For DATEDIFF, the Value 0 is the start date. Because the start date is 0, it gives you the overall number of hours between a start date of 0 and the date you give, which ends up being 1004410 hours.

DATEADD does much the same thing with the Value 0. Taking the number you just came up with (1004410 hours) and then adding it to the start date Value 0, you get the 'YYYY-MM-XX hh:mm:ss' equivalent of 1004410 hours, '2014-08-01 10:00:00'.

What the function ends up doing is taking the date time you give, converting it to a number of hours (in this case) and then converting that number of hours back into a date time, leaving out the minutes and seconds.

- Richard