comparing times but not dates - 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)

comparing times but not dates

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-10 : 07:51:04

I have
select datediff(n,'20100111 05:00','20100111 06:00')

but I really want to only count the time and not the date
so if it's

select datediff(n,'20100101 05:00','20100111 06:00')
it should still return the same 60

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-10 : 07:56:16

[code]
declare
@start datetime,
@end datetime

select @start = '20100101 05:00',
@end = '20100111 06:00'

select datediff(n,
@start - dateadd(day, datediff(day, 0, @start), 0),
@end - dateadd(day, datediff(day, 0, @end), 0))
[/code]

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-10 : 08:03:29

this doesn't work for me and
declare
@start datetime,
@end datetime

select @start = '20100101 05:00',
@end = '20100111 06:00'
select dateadd(day, datediff(day, 0, @start), 0)

returns 2010-01-01 00:00:00.000 so you end up with no times - I want to just compare the times.

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-10 : 08:33:25

i figured it out by doing

select @startingtime=convert(VARCHAR(20),getdate(),102) + ' '+ convert(VARCHAR(20),@starttime,108)
and then comparign it with the same dates.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-01-10 : 08:50:09

the query i posted does not give you the result that you want ?

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-27 : 03:04:21

quote:
Originally posted by khtan

the query i posted does not give you the result that you want ?

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

OP doesn't seem to follow all his questions

Madhivanan

Failing to plan is Planning to fail

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-01-27 : 03:18:35

sorry didn't see this till now
no khtan yours didn't give me the results i wanted - I wanted just the time which i got by doing

select @startingtime=convert(VARCHAR(20),getdate(),102) + ' '+ convert(VARCHAR(20),@starttime,108)
and then comparign it with the same dates.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-01-27 : 03:28:37

Keep it simple. Just take the modula value for how many minutes there are per day, which is 1,440.

SELECT	DATEDIFF(MINUTE, '20100101 05:00', '20100111 06:00') % 1440

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources