date diff problem - 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)

date diff problem

Author

Topic

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-08-24 : 05:28:52

Hi,

I'm trying to get records from my database where records are no older than the last 30 days. I have a column called arrival_time. this holds the date time when a person arrived. Now I want to write a sql select statement that does just this. I have written the following.

SELECT *
FROM clicky_recent_visitors
WHERE arrival_time >=
(SELECT DATEADD(day,-2, (SELECT MAX(arrival_time) FROM clicky_recent_visitors)) AS "-30 Days")
order by arrival_time asc;

However this staement seems to not bring out a few records that have their date as:

22/08/2009 00:02:00
22/08/2009 05:39:00
22/08/2009 08:18:00
22/08/2009 09:02:00

it brings back all the records after
22/08/2009 11:01:00...

I'm a bit confdes why it wo't bring back all the records from the 22nd onwnards. Should bring back all from
22/08/2009 00:00:00 onwards really..

Can anyone please help me?

Thx

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-08-24 : 05:33:33

[code]SELECT *
FROM clicky_recent_visitors
WHERE arrival_time >= (SELECT (SELECT MAX(datediff(day, 2, arrival_time)) FROM clicky_recent_visitors)) AS [-30 Days])
order by arrival_time asc;[/code]

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

zubair
Yak Posting Veteran

67 Posts

Posted - 2009-08-24 : 05:49:17

Thanks Peso!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources