SQL DateAdd ????

what is the final requirement..?

so from that data, and selecting on the date 12/22/2009, you want one of (randomly)
3568597 12/22/2009 and
3570011 12/22/2009

and either one of

3570482 12/23/2009 and
3570491 12/23/2009

and a null return (as there is no record for 12/24/2009)

Or do you mean next  working day rather than next day?
Is there anything you're not telling us which might enable us to choose which of the 4 random combinations your current data gives us is the 'right' answer?

I see I am leaving out alot....

what I am looking for is  the  highest "Sequence" for at least 3 days out, or as many different dates there are. All seperate records

Attached code should show the general principle.

select 3568597 as Sequence,  cast('12/22/2009' as varchar(10)) as ShipDate into #tmp
union select 3570011 ,'12/22/2009'
union select 3570482 ,'12/23/2009'
union select 3570491 ,'12/23/2009'
union select 50994   ,'01/04/2010'
union select 51002   ,'01/04/2010'

select max(sequence),cast(shipdate as datetime) as shipdate from #tmp
where cast(shipdate as datetime) >= dateadd(day,datediff(day,'20090101',getdate()),'20090101')
group by cast(shipdate as datetime)
order by cast(shipdate as datetime)

drop table #tmp

Select allOpen in new window

OK, Previous was concentrating on
-- converting
-- datetime (whithout hours)
Introducint max() and group by get's you ONE record per date and the highest sequence

-- What you need: where shipdate is today, tomorow and day after tomorow
declare @today as datetime;
declare @todayPlus2 as datetime;
select @today = convert(datetime,convert(char,getdate(),101),101);
select @todayPlus2 = convert(datetime,convert(char,getdate() + 2,101),101);

select x.shipdate, max(sequence)
from x
where convert(datetime,x.shipdate,101) between @today and @todayPlus2;
group by x.Shipdate

go

group by can give a false idee off ordening +  I left a ;

about sollution @PCIIan,
-- cast in SELECT and in GROUP BY are not needed
-- with CAST you trust the automatic date-conversion, it's safer to use CONVERT and provide the format in which the date is , 101 stands for mm/dd/yyyy  US-format

select 3568597 as Sequence
,  cast('12/22/2009' as varchar(10)) as ShipDate into #tmp 
union select 3570011 ,'12/22/2009' 
union select 3570482 ,'12/23/2009' 
union select 3570491 ,'12/23/2009' 
union select 4570482 ,'12/24/2009' 
union select 4570491 ,'12/24/2009' 
union select 5570482 ,'12/25/2009' 
union select 5570491 ,'12/25/2009' 
union select 6570482 ,'12/26/2009' 
union select 7570482 ,'12/27/2009' 
union select 50994   ,'01/04/2010' 
union select 51002   ,'01/04/2010' 
 
declare @today as datetime;
declare @todayPlus2 as datetime;
select @today = convert(datetime,convert(char,getdate(),101),101);
select @todayPlus2 = convert(datetime,convert(char,getdate() + 2,101),101);

select x.shipdate, max(sequence) 
from #tmp as x
where convert(datetime,x.shipdate,101) between @today and @todayPlus2
group by x.Shipdate
order by convert(datetime,x.Shipdate,101)
; 
drop table #tmp;
GO

Select allOpen in new window

@PCCian  
I fully agree with the upstream-idea  the less conversions the better (performance). With this 'mantra' in mind my comment was given but indeed probably what shortsighted to this query alone.  

But when in that table the shipdate is put into a varchar I expect them to do so on other places so returning a datetime to compare with a varchar will cause an implicit type-conversion. And there is only one thing worse than putting things in wrong data-types and that is comparing different data-types without explicitly CAST/CONVERT it to the data-type needed.

Off topic
a varchar for a date? unusable for sorting, unless you write it the yyyy/mm/dd - way. Still ask myself after half a century of computers why the IT-world never had the users convinced to addapt a format that triggers less problems (sorting and redional-dependend formats).