subtracting date and time in sql 2008

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

subtracting date and time in sql 2008

Author

Topic

andrewnm
Starting Member

5 Posts

Posted - 2013-06-26 : 03:54:53

I currently have 2 columns (starttime & endtime)storing date and time. i have done a subtraction between the 2 using datediff(minute,endtime,starttime)but i am getting negative results i.e -33

I want the results to look like this(08:30:00) i.e. hh:mm:ss

please advice.thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 04:00:36

how do you think that should be the value returned? what were the values for start and endtime?
can you try this and see if this is what you're after?


select convert(varchar(8),dateadd(minute,datediff(minute,starttime,endtime),0),108)
from table

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

andrewnm
Starting Member

5 Posts

Posted - 2013-06-26 : 04:27:38

thanks although after running the query the results are are not accurate. they all start with 23 for hours, i.e 23:54:00

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 04:30:18

then at least show us some values for start and endtimes and then show us what should be your expected output.
Otherwise we can only keep guessing as we dont know hos the values are nor can we see your system!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-06-26 : 04:35:01

if you end time is less than your start time, add 1 day to your end time


datediff(minute, 
         starttime, 
         case when starttime > endtime then dateadd(day, 1, endtime) else endtime end)

KH
[spoiler]Time is always against us[/spoiler]

andrewnm
Starting Member

5 Posts

Posted - 2013-06-26 : 04:49:56

please find the result and date in the starttime and endtime collumns.

RESULT STARTTIME ENDTIME

23:56:00 2012-03-11 11:29:27.823 2012-03-11 11:33:54.403
23:54:00 2012-03-11 11:56:12.170 2012-03-11 12:02:04.903
23:54:00 2012-03-11 11:56:57.060 2012-03-11 12:02:06.577
23:54:00 2012-03-11 12:09:17.513 2012-03-11 12:15:33.980
23:56:00 2012-03-11 11:45:25.997 2012-03-11 11:49:58.543
23:53:00 2012-03-11 12:13:53.263 2012-03-11 12:20:07.967

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-26 : 05:32:07

[code]
SELECT STARTTIME,ENDTIME,CONVERT(varchar(8),DATEADD(minute,-1 * DATEDIFF(minute,STARTTIME,ENDTIME),1),108) AS RESULT
FROM Table
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources