How to Subtract 2 time - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

How to Subtract 2 time

Author

Topic

mvijayrkumar
Starting Member

6 Posts

Posted - 2009-01-29 : 04:43:30

Hiii

Guys..can u pls help me How to subtract 2 time using select query...

ie..
i have StartTime - 09:00 ,PolicyTime- 18:00

i need to get output in the same format itself..... (hh:mm)

Note: No need of Seconds...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 04:45:11

What is the datatype used to store "times"?

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-29 : 04:51:45

declare @fromtime datetime,@totime datetime
set @fromtime = '09:00'
set @totime = '18:00'

select left(convert(varchar(32),(@totime -@fromtime),108),5)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 04:53:12

Nageswar, what if the "times" are stored as varchar(5)?

E 12°55'05.63"
N 56°04'39.26"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-01-29 : 04:54:41

declare @fromtime datetime,@totime datetime
set @fromtime = '09:00'
set @totime = '18:00'

select convert(varchar(5),(@totime -@fromtime),108)

Ok this is also correct

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-29 : 04:56:02

try this once
declare @starttime datetime,@endtime datetime
select @starttime = '3/3/2008 9:00', @endtime = '3/3/2008 18:12'
select convert(decimal(18,2),replace(left(convert(varchar(32),@endtime,108),5),':','.')) - convert(decimal(18,2),replace(left(convert(varchar(32),@starttime,108),5),':','.'))

declare @starttime varchar(32),@endtime varchar(32)
select @starttime = '3/3/2008 9:00', @endtime = '3/3/2008 18:12'
select convert(decimal(18,2),replace(left(convert(varchar(32),convert(datetime,@endtime),108),5),':','.')) - convert(decimal(18,2),replace(left(convert(varchar(32),convert(datetime,@starttime),108),5),':','.'))

mvijayrkumar
Starting Member

6 Posts

Posted - 2009-01-29 : 05:04:22

Type is nvarchar

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:04:48

quote:
Originally posted by Nageswar9

declare @fromtime datetime,@totime datetime
set @fromtime = '09:00'
set @totime = '18:00'

select convert(varchar(5),(@totime -@fromtime),108)

Ok this is also correct

Still datetime variables...

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-29 : 05:06:56

declare @starttime nvarchar(32),@endtime nvarchar(32)
select @starttime = '9:00', @endtime = '18:12'
select replace(convert(decimal(18,2),replace(left(convert(varchar(32),convert(datetime,@endtime),108),5),':','.')) - convert(decimal(18,2),replace(left(convert(varchar(32),convert(datetime,@starttime),108),5),':','.')),'.',':')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-29 : 05:34:36

Shorter...

DECLARE	@StartTime NVARCHAR(5),
	@PolicyTime NVARCHAR(5)

SELECT	@StartTime = '9:34',
	@PolicyTime = '16:22'

SELECT CONVERT(CHAR(5), CAST(@PolicyTime AS DATETIME) - CAST(@StartTime AS DATETIME), 108)

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources