datediff function with hrs and minutes

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

General SQL Server Forums

New to SQL Server Programming

datediff function with hrs and minutes

Author

Topic

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-02-09 : 16:03:24

I have two date columns one is sent_date and other is approved_date
my requirment is to find the difference between the two dates
which can be minutes/hrs/days.
using datediff function iam able to get it in minusts or hrs but my
output should be of the format hh:mm
23:10 (ie 23 hrs and 10 min) or say
48:00 (for 2 days)

sample date
sent_date approved_date
2/28/06 11:06 2/28/06 11:39
2/2/06 17:42 2/2/06 18:03
2/8/06 16:55 2/8/06 17:38
1/27/06 17:00 1/27/06 17:54
1/26/06 12:08 1/26/06 12:09
2/28/06 15:46 2/28/06 16:26
1/23/06 10:01 1/23/06 10:43
1/26/06 13:46 1/26/06 13:59
1/13/06 13:51 1/13/06 14:47

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-02-09 : 16:19:11

[code]
DECLARE @diffInMins INT
SELECT @diffInMins = 150
SELECT convert(VARCHAR(5), @diffInMins/60) + ':' + convert(VARCHAR(5), @diffInMins%60)
SELECT @diffInMins = 500
SELECT convert(VARCHAR(5), @diffInMins/60) + ':' + convert(VARCHAR(5), @diffInMins%60)
SELECT @diffInMins = 2000
SELECT convert(VARCHAR(5), @diffInMins/60) + ':' + convert(VARCHAR(5), @diffInMins%60)
SELECT @diffInMins = 45
SELECT convert(VARCHAR(5), @diffInMins/60) + ':' + convert(VARCHAR(5), @diffInMins%60)
[/code]

Go with the flow & have fun! Else fight the flow

blog thingie: http://weblogs.sqlteam.com/mladenp

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-02-09 : 16:32:38

thank you spirit1 but i dont understand it.
datediff (hh,sent_date,approved_date) is giving me the diff in hours but according to the data i have it can be only minutes.

let me tell u in details
the data in on column is like this "01/20/2006 15:59:47 LDURB" of datatype varchar
and iam taking the first 16 characters into a date column.
there are 3 columns similar to this that i have to convert to date.
after converting to date i have to display the difference in the format HH:MM in the view

vasu4us
Posting Yak Master

102 Posts

Posted - 2007-02-09 : 16:52:22

sorry i was little confused now i got it thank you.
now i have to figure it out how i can use it in my report.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-09 : 17:26:35

Or this?

-- prepare sample data
declare	@t table(sent_date datetime, approved_date datetime)

insert	@t
select	'2/28/06 11:06', '2/28/06 11:39' union all
select	'2/2/06 17:42', '2/2/06 18:03' union all
select	'2/8/06 16:55', '2/8/06 17:38' union all
select	'1/27/06 17:00', '1/27/06 17:54' union all
select	'1/26/06 12:08', '1/26/06 12:09' union all
select	'2/28/06 15:46', '2/28/06 16:26' union all
select	'1/23/06 10:01', '1/23/06 10:43' union all
select	'1/26/06 13:46', '1/26/06 13:59' union all
select	'1/13/06 13:51', '1/13/06 14:47'

-- show the output
select	sent_date,
	approved_date,
	convert(varchar(5), dateadd(minute, datediff(minute, sent_date, approved_date), 0), 108)
from	@t

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources