Date And Time Calculations

Hi
I have to calculate the data and time differences between 2 different
fields. I'm using the Datediiff, function, for each denomination.
i,e
I have a DateDiff for the:
Days
Hours
Minutes
This works perfectly fine, except it brings back the difference for each
type:
i.e For a date and time difference of 1 Day, 1 Hour and 1 Minute it brings
me back :
DAYS : 1
HOURS : 24
MINUTES :1501
How do I work with this then, since 2 Day, 2 Hours and 2 Minutes, would be :
DAYS : 2
HOURS : 48
MINUTES : 2882
Whats the easiest way to convert this values in to one readable value?
Any help would be much appreciated.
--
Kind Regards
Rikesh
(W2K-SP3 / SQL2K)one way would be to start with the DateDiff in minutes:
then proceed as follows:
DECLARE @.min int, @.hr int, @.day int
SET @.min = 3002
SELECT @.day = @.min/(1440), @.min = @.min % 1440
SELECT @.hr = @.min/60, @.min = @.min % 60
SELECT @.day, @.hr, @.min
>--Original Message--
>Hi
>I have to calculate the data and time differences between
2 different
>fields. I'm using the Datediiff, function, for each
denomination.
>i,e
>I have a DateDiff for the:
>Days
>Hours
>Minutes
>This works perfectly fine, except it brings back the
difference for each
>type:
>i.e For a date and time difference of 1 Day, 1 Hour and 1
Minute it brings
>me back :
>DAYS : 1
>HOURS : 24
>MINUTES :1501
>How do I work with this then, since 2 Day, 2 Hours and 2
Minutes, would be :
>DAYS : 2
>HOURS : 48
>MINUTES : 2882
>Whats the easiest way to convert this values in to one
readable value?
>Any help would be much appreciated.
>
>--
>Kind Regards
>Rikesh
>(W2K-SP3 / SQL2K)
>
>.
>|||Thanks for the suggestion Joe, I got a solution in the form of a UDF, here's
the code:
CREATE FUNCTION dbo.minutesToWords
(
@.minutes INT
)
RETURNS VARCHAR(255)
AS
BEGIN
DECLARE @.word VARCHAR(255)
IF @.minutes <= 0
SET @.word = 'Negative minutes?'
ELSE
BEGIN
SET @.word = ''
IF @.minutes >= (24*60)
SET @.word = @.word + RTRIM(@.minutes/(24*60))+' day(s), '
SET @.minutes = @.minutes % (24*60)
IF @.minutes >= 60
SET @.word = @.word + RTRIM(@.minutes/60)+' hour(s), '
SET @.minutes = @.minutes % 60
SET @.word = @.word + RTRIM(@.minutes)+' minute(s).'
END
RETURN @.word
END
GO
DECLARE @.diff INT
SET @.diff = DATEDIFF(MINUTE, '2003-11-30 5:43 PM', '2003-12-01 7:45 PM')
SELECT dbo.minutesToWords(@.diff)
SET @.diff = DATEDIFF(MINUTE, '2003-11-24 4:43 AM', '2003-12-01 7:45 PM')
SELECT dbo.minutesToWords(@.diff)
DROP FUNCTION dbo.minutesToWords
GO
But thanks for time in the forst place.
Cheers
Rikesh
"joe chang" <anonymous@.discussions.microsoft.com> wrote in message
news:a7e701c3b835$9e1b42b0$a601280a@.phx.gbl...
> one way would be to start with the DateDiff in minutes:
> then proceed as follows:
> DECLARE @.min int, @.hr int, @.day int
> SET @.min = 3002
> SELECT @.day = @.min/(1440), @.min = @.min % 1440
> SELECT @.hr = @.min/60, @.min = @.min % 60
> SELECT @.day, @.hr, @.min
>
> >--Original Message--
> >Hi
> >
> >I have to calculate the data and time differences between
> 2 different
> >fields. I'm using the Datediiff, function, for each
> denomination.
> >
> >i,e
> >
> >I have a DateDiff for the:
> >Days
> >Hours
> >Minutes
> >
> >This works perfectly fine, except it brings back the
> difference for each
> >type:
> >
> >i.e For a date and time difference of 1 Day, 1 Hour and 1
> Minute it brings
> >me back :
> >
> >DAYS : 1
> >HOURS : 24
> >MINUTES :1501
> >
> >How do I work with this then, since 2 Day, 2 Hours and 2
> Minutes, would be :
> >
> >DAYS : 2
> >HOURS : 48
> >MINUTES : 2882
> >
> >Whats the easiest way to convert this values in to one
> readable value?
> >
> >Any help would be much appreciated.
> >
> >
> >--
> >Kind Regards
> >
> >Rikesh
> >(W2K-SP3 / SQL2K)
> >
> >
> >
> >.
> >