How to extract hours, minutes and seconds out of a difference between 2 dates?

• Eric Krejci

  Newbie

  Points: 2

  More actions

  December 8, 2004 at 2:19 am

  #165597

  Hello,

  I'm stuck with this one. I've 2 date field (start date and end date) I want to get the difference between those 2 in this format:

  Hours:Min:seconds

  I tried the DATEDIFF with the DATEPART without any success.

  Has someone any idea?

  Thanks in advance.

  Eric K.

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  December 8, 2004 at 3:26 am

  #533002

  This example doesn't really make sense, but I couldn't think of a better one right now

  USE NORTHWIND

  GO

  SELECT REPLACE(STR(DATEDIFF(ss,orderdate,shippeddate) / 3600,4),' ',0)+

   ':' +

   REPLACE(STR((DATEDIFF(ss,orderdate,shippeddate) / 60)% 60,2),' ',0) +

   ':' +

   REPLACE(STR(DATEDIFF(ss,orderdate,shippeddate) % 60,2),' ',0)

  FROM

   Orders

  You might have to tweak this one

  SELECT REPLACE(STR(DATEDIFF(ss,orderdate,shippeddate) / 3600,4),' ',0)+

  according to your needs.

  HTH

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Eric Krejci

  Newbie

  Points: 2

  More actions

  December 8, 2004 at 6:31 am

  #533020

  Thanks Frank, it works fine.

  BR

  Eric