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

User · Answer

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

User · Answer

Have you tried  SELECT DATEADD mi  -15  2000-01-01 08 30 00     DATEDIFF is the difference between 2 dates

User · Answer

You want to use DATEADD  using a negative duration  e g   DATEADD minute  -15   2000-01-01 08 30 00

User · Answer

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

User · Answer

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

[sql-server] Subtract minute from DateTime in SQL Server 2005

OUTPUT:

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to sql-server-2005

Examples related to dateadd