DateTime format to HH:MM:SS or HH:MM

• notes4we

  SSCommitted

  Points: 1565

  More actions

  April 13, 2009 at 9:13 am

  #129300

  Hello,

  I have two datetime fields and I want to find a difference between those two in either hh:mm:ss or hh:mm format.

  Is there a way to achieve that in SQL?

  The query is:

  SELECT

  min(starttime) as Minimum,

  max(endtime) as Maximum,

  min(starttime) - max(endtime) as Difference

  FROM [DatabaseName].[dbo].[TableName]

  WHERE

  starttime

  BETWEEN

  '2009-04-13 00:00:00.000'

  AND

  '2009-04-13 08:30:00.000'

  Can you please help me modify the query so that I can get the results in either hh:mm:ss format or hh:mm format.

  Thank you.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 13, 2009 at 9:16 am

  #976679

  Do you still want the results in HH:MM or HH:MM:SS format if the hours exceeds 24 hours? For example 2009-04-12 10:00:00 to 2009-04-13 10:05:00 would be 24:05.

• notes4we

  SSCommitted

  Points: 1565

  More actions

  April 13, 2009 at 9:35 am

  #976690

  Yes. I want it in HH:MM:SS format, because most of the times I will be having less than 24 hours, except for some cases. So, thats fine with me.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 13, 2009 at 9:47 am

  #976698

  Does this help you get started?

  declare @Date1 datetime,

  @Date2 datetime;

  select

  @Date1 = '2009-04-13 10:00:00',

  @Date2 = '2009-04-13 10:15:16';

  select

  datediff(ss, @Date1, @Date2),

  datediff(ss, @Date1, @Date2) % 60 as Seconds,

  (datediff(ss, @Date1, @Date2) / 60) % 60 as Minutes,

  (datediff(ss, @Date1, @Date2) / (60 * 60)) % 60 as Hours

  ;

• notes4we

  SSCommitted

  Points: 1565

  More actions

  April 13, 2009 at 9:49 am

  #976700

  Thank you Lynn.

  I got one more help which mentioned:

  SELECT

  MIN(starttime) as Minimum,

  MAX(endtime) as Maximum,

  CONVERT

  (varchar,(MIN(starttime) - MAX(endtime)),108) as Diff

  FROM

  dbo.TableName

  WHERE

  startime

  BETWEEN

  '2009-04-13 00:00:00.000'

  AND

  '2009-04-13 08:30:00.000'

  This is also working fine.

• notes4we

  SSCommitted

  Points: 1565

  More actions

  April 13, 2009 at 9:56 am

  #976704

  Now, I want to store the results of this query in a variable in SSIS.

  But I am getting the results as: 16:52:45

  I do not understand the datatype that I can have for storing this as a variable in SSIS.

  Any help!