How can I format duration between two datetime

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 2005 Forums

Transact-SQL (2005)

How can I format duration between two datetime

Author

Topic

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2009-08-27 : 06:46:23

I am trying to write a function that receive 2 parameters start date and end date then output format duration between two datetime like this
(numberOfYear) year(s), (numberOfMonth)month(s), (numberOfDay)day(s), (numberOfHour)hour(s), (numberOfMinute)minute(s)

Example:

'10/20/2006 08:35:00', '12/30/2008 09:40:00'

The output like:

2 year(s), 2 month(s), 10 day(s), 1 hour(s), 3 minute(s)

How can I write a function that format output like this?
Thanks in advance

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-27 : 07:11:00

declare @t datetime, @s datetime
select @t = '10/20/2006 08:35:00',@s = '12/30/2008 09:40:00'

select datediff(year,@t,@s) as year,
datediff(month,@t,@s) %12 as month,
datediff(day,@t,@s)%365 as day,
datediff(hour,@t,@s)%24 as hours,
datediff(n,@t,@s)%60 as minutes

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2009-08-27 : 07:31:21

But the result(s) is

2 year(s), 2 month(s), 72 day(s), 1 hour(s), 5 minute(s)

The day must be 10 day(s) not 72 day(s)

Thanks for your reply.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-28 : 01:20:30

[code]
declare @t datetime, @s datetime ,@t1 datetime,@s1 datetime
select @t = '10/20/2006 08:35:00',@s = '12/30/2008 09:40:00'

select @t1 = cast(month(@t)as varchar(32))+'-01-'+cast(year(@s)as varchar(32)),
@s1 = cast(month(@s)as varchar(32))+'-01-'+cast(year(@s)as varchar(32))
select @t1,@s1,datediff(day,@t1,@s1)%365 as day

select datediff(year,@t,@s) as year,
datediff(month,@t,@s) %12 as month,
datediff(day,@t,@s)%365 -datediff(day,@t1,@s1)%365 as day,
abs(datepart(day,@t) - datepart(day,@s)) as day,
datediff(hour,@t,@s)%24 as hours,
datediff(n,@t,@s)%60 as minutes
[/code]

nguyenvantruongthang
Starting Member

12 Posts

Posted - 2009-08-28 : 04:32:18

Great! Exactly SQL command that I'm looking for.
Thanks in advance!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-08-31 : 00:16:58

quote:
Originally posted by nguyenvantruongthang

Great! Exactly SQL command that I'm looking for.
Thanks in advance!

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources