Problem Comparing Dates In Store Procedure

Hi,





Below is my store procedure. I am trying to capture all orders between a startdate and a enddate. When I use the Where #1(see below- I have 2 where clauses) it allows me to go from one year to another. For example, startdate of 2/18/2003 - enddate of 2/19/2004 will return all orders in my table except for 2/19/2004. Probably because of the time portion.


When I use the Where #2, it doesn't go back a full year. The startdate is reacting like 2/18/2004 and not 2003. The records I get are only for dates 2/18/2004-2/19/2004.





How do I create a where clause which will go back a year or more and include the enddate orders? Any help will be very much appreciated.





Thanks,





poc1010








alter Procedure GetOrdersBetweenDates





@startdatesmalldatetime=null,


@enddatesmalldatetime=null





as





select o.orderid,(convert(varchar(12),o.orderdate,101)) as Date


,o.orderamt,o.shipamt,o.njtaxamt,o.totalamt


,c.bfirstname+' '+c.blastname as BName,c.baddress1,c.baddress2


,c.bcity,c.bstate,c.bzip


,c.sfirstname+' '+c.slastname as SName,c.saddress1,c.saddress2


,c.scity,c.sstate,c.szip


from orders o inner join customers c on o.orderid=c.orderid


1.)where o.orderdate between @startdate and @enddate


2.)where convert(varchar(12),o.orderdate,101) between


convert(varchar(12),@startdate,101) and convert(varchar(12),@enddate,101)


order by o.orderid





exec getordersbetweendates '2/18/2003','02/19/2004'