Subtract minute from DateTime in SQL Server 2005

Question

Suppose I have a datetime field whose value is 2000-01-01 08:30:00 and a duration field whose value is say 00:15 (meaning 15 minutes)

If I subtract these two, I should get 2000-01-01 08:15:00

Also if I want to subtract 1:15 (means 1 hour 15 minutes), the output should be 2000-01-01 07:15:00

I am trying SELECT DATEDIFF(minute, '00:15','2000-01-01 08:30:00');

But the output is 52595055. How can i get the desired result?

N.B.~ If I do SELECT dateadd(minute, -15,'2000-01-01 08:30:00'); , I will get the desired result but that involves parsing the minute field.

Edit:

As per the answers, every one is suggesting converting everything into minutes and then to subtract - so if it is 1:30, i need to subtract 90 minutes. That's fine. Any other way without converting to minutes?

Solution 1

SELECT DATEADD(minute, -15, '2000-01-01 08:30:00'); 

The second value (-15 in this case) must be numeric (i.e. not a string like '00:15'). If you need to subtract hours and minutes I would recommend splitting the string on the : to get the hours and minutes and subtracting using something like

SELECT DATEADD(minute, -60 * @h - @m, '2000-01-01 08:30:00'); 

where @h is the hour part of your string and @m is the minute part of your string

EDIT:

Here is a better way:

SELECT CAST('2000-01-01 08:30:00' as datetime) - CAST('00:15' AS datetime)

Solution 2

You want to use DATEADD, using a negative duration. e.g.

DATEADD(minute, -15, '2000-01-01 08:30:00') 

Solution 3

Have you tried

SELECT DATEADD(mi, -15,'2000-01-01 08:30:00')

DATEDIFF is the difference between 2 dates.

Solution 4

I spent a while trying to do the same thing, trying to subtract the hours:minutes from datetime - here's how I did it:

convert( varchar, cast((RouteMileage / @average_speed) as integer))+ ':' +  convert( varchar, cast((((RouteMileage / @average_speed) - cast((RouteMileage / @average_speed) as integer)) * 60) as integer)) As TravelTime,

dateadd( n, -60 * CAST( (RouteMileage / @average_speed) AS DECIMAL(7,2)), @entry_date) As DepartureTime 

OUTPUT:

DeliveryDate                TravelTime             DepartureTime
2012-06-02 12:00:00.000       25:49         2012-06-01 10:11:00.000

Solution 5

Use DATEPART to pull apart your interval, and DATEADD to subtract the parts:

select dateadd(
     hh,
    -1 * datepart(hh, cast('1:15' as datetime)),
    dateadd(
        mi,
        -1 * datepart(mi, cast('1:15' as datetime)),
        '2000-01-01 08:30:00'))

or, we can convert to minutes first (though OP would prefer not to):

declare @mins int
select @mins = datepart(mi, cast('1:15' as datetime)) + 60 * datepart(hh, cast('1:15' as datetime)) 
select dateadd(mi, -1 * @mins, '2000-01-01 08:30:00')

View more solutions

Comments

[OMG Ponies]

You'd have been better off to store full DATETIME values for start and end

[priyanka.bangalore]

I am sorry but I was editing while you posted the answer. I donot want to parse that.

[priyanka.bangalore]

So you mean to say that to convert everything into minutes and then to subtract like if it is 1:30 means i need to subtract 90 minutes. That's fine. Any other way without converting to minutes?

[priyanka.bangalore]

So you mean to say that to convert everything into minutes and then to subtract like if it is 1:30 means i need to subtract 90 minutes. That's fine. Any other way without converting to minutes?

[priyanka.bangalore]

So you mean to say that to convert everything into minutes and then to subtract like if it is 1:30 means i need to subtract 90 minutes. That's fine. Any other way without converting to minutes?

[priyanka.bangalore]

So you mean to say that to convert everything into minutes and then to subtract like if it is 1:30 means i need to subtract 90 minutes. That's fine. Any other way without converting to minutes?

[Martin Booth]

Sorry I edited my answer after I realised I hadn't provided a very good solution; multiplying by 1440 (the number of minutes in a day) converts days to minutes. Previously I was casting a datetime to a float to get a number representing days hence the conversion.. I have since changed my answer so that it does not require conversion to minutes at all