SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 subtracting date and time in sql 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

andrewnm
Starting Member

Kenya
5 Posts

Posted - 06/26/2013 :  03:54:53  Show Profile  Reply with Quote
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

India
52323 Posts

Posted - 06/26/2013 :  04:00:36  Show Profile  Reply with Quote
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
Go to Top of Page

andrewnm
Starting Member

Kenya
5 Posts

Posted - 06/26/2013 :  04:27:38  Show Profile  Reply with Quote
thanks although after running the query the results are are not accurate. they all start with 23 for hours, i.e 23:54:00
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/26/2013 :  04:30:18  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17650 Posts

Posted - 06/26/2013 :  04:35:01  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

andrewnm
Starting Member

Kenya
5 Posts

Posted - 06/26/2013 :  04:49:56  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52323 Posts

Posted - 06/26/2013 :  05:32:07  Show Profile  Reply with Quote

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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000