T-SQL datetimes substracting.

Question

I've got a column Start and column End. I want to get number of hours and minutes between them. Select cast(End - Start as varchar) gives strange results, for example 2009-07-24 06:16 - 2009-07-24 06:30 gives result 12:14AM... It is important to cast it as varchar because later I will be cutting substrings out of this string with right() and left(). How can I get the proper result?

Answer 1

cast it as numeric, otherwise it tries to cast it as a date.

Answer 2

You can use the DATEDIFF function

SELECT DATEDIFF(mi, Start, End)

This will give you the number of minutes between Start and End, you can then convert this to hours and minutes.

Answer 3

use DATEDIFF to calculate difference between two dates in required units. Then convert the result to varchar.

Answer 4

You should probably do a datediff

Answer 5

The reason for the issue you are seeing is the way SQL handles the two parts of the way it stores a date.

A date is stored as two part, inside a numerical value. The First part, the integer part stores the day, and the second part, the decimal part stores the time.

If your two dates were equivalent to 1.50 and 1.25 then 1.25 - 1.50 = -0.25 - this is then split up into two parts, -0 Days, and .25 Time. 0.25 of a day would translate to 6 hours, so would give a time of 6.00AM.

To get proper date manipulation you should always use DATEDIFF and DATEPART.

Answer 6

varchars should never be used for date manipulation. use datediff like this:

declare @date1 datetime, @date2 datetime
select @date1 = getdate(), @date2 = DATEADD(n, 145, @date1)
SELECT @date1, @date2

select  DATEDIFF(n, @date1, @date2) as DiffMinutes,
    DATEDIFF(n, @date1, @date2)/60 as DiffHours, 
    DATEDIFF(n, @date1, @date2)%60 as DiffMinutesOfTheHour

Answer 7

for hours and minutes that are relative to each other: Use:

DECLARE @StartDate  datetime
DECLARE @EndDate    datetime

set @StartDate ='2009-07-24 06:16'
SET @EndDate   ='2009-07-24 07:30 '
 SELECT DATEDIFF(HH,@StartDate,@EndDate) AS Hours,
        DATEDIFF(mi,@StartDate,@EndDate)-DATEDIFF(HH,@StartDate,@EndDate)*60 AS Minutes

returns:

Hours       Minutes
----------- -----------
1           14

(1 row(s) affected)