Combine Date & Time parts for Two Dates, then subtract

• BGEast

  SSC Enthusiast

  Points: 114

  More actions

  December 15, 2020 at 3:48 pm

  #3820739

  Hi,   I'm trying to combine a Date and a Time field into a DateTime, and then again for two more Date and Time fields, and then subtract the two combined DateTime fields within one select field of the SELECT statement...  I can combine through CAST a Date field with a Time field, and I can through DateDiff subtract two dates, but I can't figure out how to combine in the one statement.. So I'm trying to combine the Cast of two time fields  with Datediff.. the below works as separate but not when I try to combine....

  Select MyDateTime1=CAST(Date1 AS DATETIME) + CAST(Time1 AS DATETIME),

  MyDateTime2=CAST(Date2 AS DATETIME) + CAST(Time2 AS DATETIME),

  DateDiff (Day,Date1,MyDate2) AS 'MyTimeSpent'

  --But I'm hoping for DateDiff (Day,MyDateTime1,MyDateTime2) AS 'MyTimeSpent'

  From .......

• Phil Parkin

  SSC Guru

  Points: 247172

  More actions

  December 15, 2020 at 4:10 pm

  #3820745

  This is untested, as I don't currently have access to SQL Server, but should work OK:

  SELECT DateDiff (Day,DateCalc.MyDateTime1,DateCalc.MyDateTime2) AS 'MyTimeSpent'
  FROM sometable t1
  CROSS APPLY (SELECT MyDateTime1=CAST(t1.Date1 AS DATETIME) + CAST(t1.Time1 AS DATETIME),
   MyDateTime2=CAST(t1.Date2 AS DATETIME) + CAST(t1.Time2 AS DATETIME)) DateCalc

  ---

• BGEast

  SSC Enthusiast

  Points: 114

  More actions

  December 15, 2020 at 6:18 pm

  #3820798

  Thanks!!  That appears to work...  Now I just have to figure out how to get 'MyTimeSpent' to display as day plus hours or as full day with decimal..  Such as 2.75 (days) rather than showing as 2 (days) currently.   Thanks again.

• Jeff Moden

  SSC Guru

  Points: 1004627

  More actions

  December 16, 2020 at 4:23 am

  #3820977

  BGEast wrote:

  Thanks!!  That appears to work...  Now I just have to figure out how to get 'MyTimeSpent' to display as day plus hours or as full day with decimal..  Such as 2.75 (days) rather than showing as 2 (days) currently.   Thanks again.

  You should have asked for that first.

  Just use the power that's built into the DATETIME datatype that they (very unfortunately) destroyed in the "newer" temporal datatypes.  You don't even need a DATEDIFF.

   SELECT MyTimeSpent = CONVERT(DECIMAL(9,2),
                          CONVERT(DATETIME,Date2)+CONVERT(DATETIME,Time2)
                         -CONVERT(DATETIME,Date1)-CONVERT(DATETIME,Time1))
     FROM dbo.SomeTable
  ;

  Heh... Ok... you, me, and Phil need to duck.   There will be a shedload of hoo-haa flying about concerning some supposed "Best Practice"  about not doing direct date math by people that don't know that the ANSI/ISO standards explicitly state that you can and it works just fine.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)