SQLTeam.com | Weblogs | Forums

Minutes cslculation


#1

We have four column two are dates start date and end date. Another two columns are start time and end time. How can I get the difference in minute by using for columns


Datediff
#2

Assuming the date columns are of DATE type and time columns are of TIME type, see this example:

CREATE TABLE #tmp(startDate DATE, startTime TIME, endDAte DATE, endTime TIME);
INSERT INTO #tmp VALUES ('20150611','18:38:42','20150621','11:22:07')


SELECT DATEDIFF( mi,
	 CAST( startDate AS DATETIME ) + startTime,
	 CAST( endDate AS DATETIME ) + endTime
	)
FROM #tmp;

DROP TABLE #tmp;

#3

Nicely done, James. Good to see someone that knows both implicit conversions and the joy of the good ol' fashion "Direct Datetime Math". Keeps code real easy. And, no... I don't believe in the myth of portable code, either. :mask:

As a bit of a sidebar, I still can't understand why people insist on screwing themselves to the floor by separating date from times. Except for certain far out edge cases, I've never seen it save more work than it causes.