Combine Date & Time parts for Two Dates, then subtract

Question

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 .......

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

BGEast SSC Enthusiast Points: 114 More actions · Answer 2

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: 1004324 More actions · Answer 3

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)