SQL date question

Question

I have a question. I have a SQL Server 2008 table with a field column. I have for example the Following dates:

1/1/2001
5/5/2004
8/5/2009
10/7/2011
5/5/2012
1/13/2014

Id like to be able to show all dates >= the current date (7/29/2011) as well as largest table date that is < current date. In this example, the result would be all dates >= 8/5/2009.

Can someone help guide me please??

Answer 1

select max(date) [date] from table where date < getdate()
union 
select date from table where date >= getdate()

Answer 2

If I understand correctly, you want to include the date prior to the current date. GETDATE() will get the current date (with time). If you're alright with that, then this should work. Otherwise, you may have to parse out just the date from GETDATE()

SELECT TheDate
FROM DateTable
WHERE TheDate >= (SELECT MAX(TheDate) FROM DateTable WHERE TheDate < GETDATE())

This gets all dates greater than or equal to the most recent date before the current date.