I have created a Model and using Report Builder to create a report. The
only thing I cannot get is the formula for the date difference. I need to
take an [Opened Date and Time] and the [Closed Date and Time] and get the
difference in DD:HH:MM.
Thanks in advance!Check this:
http://msdn2.microsoft.com/en-us/library/aa258269(SQL.80).aspx
Cheers,
MB
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||Hi
Take a look at DATEDIFF system function in the BOL.
"Cliff Parker" <cliff.parker@.stewart.com> wrote in message
news:614DB115-6BA5-4824-B4EF-5D1455F1FEC6@.microsoft.com...
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
> Thanks in advance!|||The DATEDIFF function can give you the difference in minutes.
Expressing that in the form DD:HH:MM is not so simple, as there is
nothing in SQL Server in that format. If all you need to do is
display it you could turn it into a string.
Calculating the three parts is not as simple as using datepart three
times. It requires a bit of arithmetic.
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
To put this into the DD:HH:MM format we could use something like:
DECLARE @.from datetime
DECLARE @.to datetime
SET @.from = '20060704 8:00'
SET @.to = '20061031 10:30'
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT DATEDIFF(minute,@.from, @.to) / (60 * 24) as Days
SELECT DATEDIFF(minute,@.from, @.to) % 60 as Minutes
SELECT (DATEDIFF(minute,@.from, @.to) / 60) % 24 as Hours
SELECT CONVERT(varchar(6),DATEDIFF(minute,@.from, @.to) / (60 * 24))
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) / 60) %
24)+100,2)
+ ':' +
RIGHT(CONVERT(varchar(6),(DATEDIFF(minute,@.from, @.to) %
60))+100,2)
--
119:02:30
The trick used to add the leading zeroes was to add 100 before
converting to a string and taking the two rightmost characters.
Hopefully that gives you something to start with.
Roy Harvey
Beacon Falls, CT
On Wed, 21 Mar 2007 00:12:50 -0500, "Cliff Parker"
<cliff.parker@.stewart.com> wrote:
>I have created a Model and using Report Builder to create a report. The
>only thing I cannot get is the formula for the date difference. I need to
>take an [Opened Date and Time] and the [Closed Date and Time] and get the
>difference in DD:HH:MM.
>Thanks in advance!|||On Wed, 21 Mar 2007 08:02:47 -0400, Roy Harvey wrote:
>The DATEDIFF function can give you the difference in minutes.
>Expressing that in the form DD:HH:MM is not so simple, as there is
>nothing in SQL Server in that format. If all you need to do is
>display it you could turn it into a string.
>Calculating the three parts is not as simple as using datepart three
>times. It requires a bit of arithmetic.
(snip)
Hi Roy (and Cliff),
Well, for the HH:MM part, there is an alternative: compute the
difference in minutes, add that to a starting date (any date will do) at
midnight, and convert that to string using a "time only" format:
DECLARE @.from datetime;
DECLARE @.to datetime;
SET @.from = '20060704 8:00';
SET @.to = '20061031 10:30';
SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
+ CONVERT(char(5),
DATEADD(minute,
DATEDIFF(minute, @.from, @.to),
'19000101'), -- Any date will do
108);
Results in
119:02:30
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
<hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
> + CONVERT(char(5),
> DATEADD(minute,
> DATEDIFF(minute, @.from, @.to),
> '19000101'), -- Any date will do
> 108);
Using DATEDIFF for calculating days is not always correct depending on
the times of day of the two datetimes. Try it for times on either
side of midnight, such as
SET @.from = '20060704 21:00';
SET @.to = '20060705 01:30';
and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
day calculation as:
DATEDIFF(minute,@.from, @.to) / (60 * 24)
Roy Harvey
Beacon Falls, CT|||On Wed, 21 Mar 2007 17:54:53 -0400, Roy Harvey wrote:
>On Wed, 21 Mar 2007 21:27:33 +0100, Hugo Kornelis
><hugo@.perFact.REMOVETHIS.info.INVALID> wrote:
>>SELECT CAST(DATEDIFF(day, @.from, @.to) AS varchar(10)) + ':'
>> + CONVERT(char(5),
>> DATEADD(minute,
>> DATEDIFF(minute, @.from, @.to),
>> '19000101'), -- Any date will do
>> 108);
>Using DATEDIFF for calculating days is not always correct depending on
>the times of day of the two datetimes. Try it for times on either
>side of midnight, such as
>SET @.from = '20060704 21:00';
>SET @.to = '20060705 01:30';
>and it returns 1:04:30 rather than 1:04:30. Which is why I coded the
>day calculation as:
> DATEDIFF(minute,@.from, @.to) / (60 * 24)
Hi Roy,
That was a stupid error - thanks for catching it!
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||Roy,
Thanks for the information. It was right on the money. You have helped me
out big time!
Many cudos!
Cliff Parker
