time difference between two column

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

SQL Server 2008 Forums

Transact-SQL (2008)

time difference between two column

Author

Topic

megala
Starting Member

23 Posts

Posted - 2009-04-29 : 01:59:46

Hi, im looking for help on two varchar column in database that stores a duration. for exampel starttime(23:45:001) which means starts at 2pm and endtime (00:45:000). so the duration shoud be 1hour.

how can i get the value as 01.00

i found one query as

select
	
	Elapsed_Time = ET-ST

from
	(
	select
		ST=convert(datetime,epstarttime),
		ET=convert(datetime,ependtime)
		FROM episodes where epid=665
	) a

the rsult is 1899-12-31 00:59:59.000

how can i modify this to get the result as 01.00 or as 00:59

Please help.or is there a better way of doing it.

thanks a million in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-29 : 02:05:25

use datediff to find the diff in 2 datetime

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

megala
Starting Member

23 Posts

Posted - 2009-04-29 : 02:11:58

I have tried datediff but for example starttime(23:45:001) which means starts at 2pm and endtime (00:45:000)

the result the query below return is -23. but its supposed to give me 01.00. Its due to the value of 12:45am is stored as 00:45.

Select datediff(hh,epstarttime,ependtime)
FROM episodes where epid=665

any suggestion?

thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-29 : 02:17:23

as your end time is actually past midnight . . . you need to add 1day to it


Select datediff(hh,epstarttime, case when ependtime < epstarttime then dateadd(day, 1, ependtime ) else ependtime end)
FROM episodes where epid=665

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

megala
Starting Member

23 Posts

Posted - 2009-04-29 : 02:35:32

hi tan, i really appreciate your help.your suggestion works.this is what i changed.

Select datediff(mi,epstarttime, case when ependtime < epstarttime then dateadd(day, 1, ependtime ) else ependtime end)
FROM episodes where epid=1141

so now when the result i get is in minutes example 120 minutes, i need to convert it to 02.00

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-04-29 : 02:37:52

[code]
Select dateadd(minute, datediff(mi,epstarttime, case when ependtime < epstarttime then dateadd(day, 1, ependtime ) else ependtime end), 0)
FROM episodes where epid=1141
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources