0

I am trying to get cumulative DATEDIFF but am coming up with not exactly what I need.

This is the code that I am using

CONVERT(VARCHAR(30),DATEDIFF(dd,i.MODIFY_DATE,GETDATE()),121) + 'D ' + 
CONVERT(VARCHAR(30),DATEDIFF(hh,i.MODIFY_DATE,GETDATE()),121) + 'H ' + 
CONVERT(VARCHAR(30),DATEDIFF(dd,i.MODIFY_DATE,GETDATE()),121) + 'M '

the i.MODIFY_DATE = 2013-10-09 12:37:54.663

current get date = 2014-10-07 12:31:52.993

The output that I get is = 363D 8712H 363M

What I am looking for is so that the H don't go over 24 and the M doesn't go over 60. Meaning something where it takes every 60M and adds 1 to H, and the same with H but every 24 it adds to D

JohnZ
  • 382
  • 2
  • 8
  • 20
  • [Please stop using lazy shorthand like `dd` and `hh`](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/09/20/bad-habits-to-kick-using-shorthand-with-date-time-operations.aspx). There's already a mistake in your code likely due to this, that you might have caught if you had taken 4 extra nanoseconds to type out the whole words. – Aaron Bertrand Oct 07 '14 at 16:41
  • Your logic is mistaken. You tried to cal the day difference, hour difference and min difference. Note that if you 8712/24, you will get 363. My suggestion is to cal all min difference and then do a conversion from min to hour then from hour to day. – Luo Sen Oct 07 '14 at 16:52

0 Answers0