Find Date difference in m - d - h-min

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find Date difference in m - d - h-min

Author

Topic

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-01-24 : 09:28:39

Hello,

Has anyone written a function that takes two date and finds the difference between them in the following format:
#months + #days + # hours + #minutes.

I know that I can get each of these individually but I need to get one answer broken down in these components:

1/1/03 12:00 to 2/2/03 1:30 = 1 month + 1 day + 1hour + 30minutes

Thanks,
Juls.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-24 : 09:56:17

What do you mean by 1 month?

Is that how many month boundary lines you cross? For example:

1/5/03 12:00 to 2/2/03 1:30 = 1 month + -2 day + 13 hour + 30

declare @s datetime, @e datetime
select @s = '1/5/03 12:00', @e = '2/2/03 1:30'

select
    datediff(mm,@s,@e) as 'month',
    datediff(dd,@s,@e) -
        case
            when day(@s) < day(@e) then datediff(dd,@s,dateadd(mm,datediff(mm,@s,@e),@s))
            when day(@s) > day(@e) then datediff(dd,@s,dateadd(mm,datediff(mm,@s,@e),@s)-1)
            else datediff(dd,@s,@e)
        end as 'day',
    datediff(hh,@s,@e) % 24 as 'hour',
    datediff(mi,@s,@e) % 60 as 'minute'

edit: by the way there is 13.5 hours between noon and 1:30 in the morning ...

Jay White
{0}

Edited by - Page47 on 01/24/2003 09:59:02

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-01-24 : 10:50:34

Thanks,
but not exactly

I need to know how much time passed between the two dates up to the minite, so that if for example
start: 1/1/03 15:52 end 1/1/03 16:42 then answer would be 50 minutes and not 1hour and 50 minutes.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-24 : 11:09:24

so how long is a month?

Jay White
{0}

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-01-24 : 11:13:16

Month is a calendar month: Jan - 31days, Feb-28(29)days, etc...
It depends on a date.

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-24 : 11:25:04


    datediff(hh,@s,@e) % 24 / 60 as 'hour',

sorry

Jay White
{0}

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-01-24 : 11:38:01

Thanks,

But the day and month still counts wrong.. It should only count 1 if 24 hours passed between start and end dates and a whole month passed
Start: 1/31/03 11:55:00 end: 2/1/03 00:05:00 should be
0 month, 0days 0hours 10minutes

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-01-24 : 11:39:09

Probably go for something like this:


declare @s datetime, @e datetime
select @s = '1/5/03 12:00', @e = '2/2/03 1:30'
--select @s = '1972-02-29 12:00', @e = '2002-02-28 12:00'

DECLARE @month int, @sec int

SELECT @month = DATEDIFF(m, @s, @e) -
  CASE WHEN DATEADD(m, DATEDIFF(m, @s, @e), @s) > @e THEN 1 ELSE 0 END

SELECT @sec = DATEDIFF(s, DATEADD(m, @month, @s), @e)

SELECT @month / 12 AS years, @month % 12 AS months, @sec / 86400 AS days,
  (@sec / 3600) % 24 AS hours, (@sec / 60) % 60 AS minutes, @sec % 60 AS seconds

However, this will give you 30 years for the commented-out values, as would 1972-02-28 12:00, which may or may not be what you want!

Edited by - Arnold Fribble on 01/24/2003 11:40:00

Juls
Yak Posting Veteran

51 Posts

Posted - 2003-01-24 : 12:33:45

This is great Arnold THANK YOU!
I will never have dates on last day(s) of leap years, but it works great at recognizing correct number of days in a month.

Thanks so much.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources