By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 1,616 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

converting minutes to hh:mm:ss in sql

P: 1
I have query like this:
Expand|Select|Wrap|Line Numbers
  1. select SUM(DATEDIFF(MI,t.Paydate,t.DelDate)) as sum_min,
  2.        AVG( CONVERT(NUMERIC(18,2), DATEDIFF(MI,t.Paydate,t.DelDate) ) ) as avg_min
  3. from Transaction_tbl t where t.transactID in(24,25)
  4. group by t.vtid
I am getting out put like this:
Expand|Select|Wrap|Line Numbers
  1. sum_min     avg_min
  2. ----------- ---------------------------------------
  3. 26          26.000000
  4. 7           7.000000
now am getting sum_min out put in minutes,insted of geting out put in minutes i want to show in HH:mm:ss,Expected out put like this:
Expand|Select|Wrap|Line Numbers
  1. sum_min     avg_min
  2. ----------- ---------------------------------------
  3. 00:26:00          26.000000
  4. 00:07:00           7.000000
SO i tryed query like this:
Expand|Select|Wrap|Line Numbers
  1. SELECT convert(varchar(10),sum(DATEDIFF(hour,t.Paydate,t.DelDate)))+':' 
  2.       +convert(varchar(10),sum(DATEDIFF(minute,t.Paydate,t.DelDate)% 60)) + ':' 
  3.       +convert(varchar(10),sum(DATEDIFF(SECOND,t.Paydate,t.DelDate)% 60)) 
  4.        AS ' HH:MM:SS'
  5. FROM Transaction_tbl t 
  6.  
  7. WHERE t.transactID in(24,25) group by vtid
..so am getting out put like this:
Expand|Select|Wrap|Line Numbers
  1.  HH:MM:SS
  2. --------------------------------
  3. 1:26:36
  4. 1:7:25
showing 1 hr extra. how i can get proper answer
Jul 21 '13 #1
Share this Question
Share on Google+
1 Reply


Rabbit
Expert Mod 10K+
P: 12,347
I don't see what this has to do with .net. Let us know what SQL database server you're using and we can move it to the correct forum.

Please use code tags when posting code or formatted data.

That's because the datediff function rounds up. If you want it to be accurate down to the second, you need to get the difference in seconds for hours and minutes and use integer division.
Jul 21 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.