Home Forums SQL Server 2008 T-SQL (SS2K8) datediff producing erroneous result when datetime is between midnight and 1AM RE: datediff producing erroneous result when datetime is between midnight and 1AM

  • The convert you are using is only showing the hours, minutes and seconds, and the answer you want is 1 day, 1 hour, 42 minutes, and 46 seconds

    You can try something like

    select convert(varchar,DATEDIFF(mi,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000')/60) + right(CONVERT(VARCHAR(12), DATEADD(MS,DATEDIFF(ms,'2013-09-30 00:22:05.000', '2013-10-01 02:04:51.000'), 0), 114),10)

    to get hours over 24 in the hours count.

    You can also break it down yourself and avoid using date/string manipulation by using division and remainders, and converting the answers into strings