Comparing the time while disregarding the date

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

SQL Server Development (2000)

Comparing the time while disregarding the date

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-05-17 : 09:46:11

Tim writes "Hi,

I have two datetime variables and I want to compare the difference in time, regardless of when the date might be.

e.g. If I have the following two dates:

Apr 01 2006 09:30AM
Apr 13 2006 11:47AM

All I want to know is the difference in time (in minutes) between 09.30am and 11.47am.

I was just wondering if there might be an easy, painless way of doing this without lots of converting and subscripting?

Many Thanks,

Tim."

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-05-17 : 10:09:19

Create table #t (d1 datetime, d2 datetime)
Insert into #t values('Apr 01 2006 09:30AM','Apr 13 2006 11:47AM')

Select datediff(mi,CONVERT(varchar, d1, 108),CONVERT(varchar, d2, 108) ) from #t

drop table #t

Srinika

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-05-17 : 10:37:53

declare @d1 datetime
,@d2 datetime

select @d1='Apr 01 2006 09:30AM',@d2='Apr 13 2006 11:47AM'

select datediff(minute,dateadd(day,datediff(day,@d1,@d2),@d1),@d2)

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-18 : 02:38:26

http://vyaskn.tripod.com/searching_date_time_values.htm

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-18 : 05:59:11

[code]declare @dt1 datetime,
@dt2 datetime

select @dt1 = '2006-05-01 02:34:56.23',
@dt2 = '2006-04-15 02:34:56.23'

if (@dt1 - dateadd(day, datediff(day, 0, @dt1), 0)) = (@dt2 - dateadd(day, datediff(day, 0, @dt2), 0))
PRINT 'SAME TIME'
else
PRINT 'DIFF TIME'[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources