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.
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 -33I want the results to look like this(08:30:00) i.e. hh:mm:ssplease 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 MVPhttp://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 MVPhttp://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 timedatediff(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 ENDTIME23:56:00 2012-03-11 11:29:27.823 2012-03-11 11:33:54.40323:54:00 2012-03-11 11:56:12.170 2012-03-11 12:02:04.90323:54:00 2012-03-11 11:56:57.060 2012-03-11 12:02:06.57723:54:00 2012-03-11 12:09:17.513 2012-03-11 12:15:33.98023:56:00 2012-03-11 11:45:25.997 2012-03-11 11:49:58.54323: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 RESULTFROM Table[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|