Checking Date part only, of a DateTime field

I have a query like :

SELECT ..
        FROM ...
        WHERE ...
        AND ISNULL(ICA.actual_appearance_date, ICA.scheduled_appearance_date)  
            BETWEEN LO.order_start_date 
                AND ISNULL(LO.actual_expiry_date, LO.expected_expiry_date)

Is there a neat way to only check that the actual_appearance_date is between the start and end date, but not include time?

So:

Appearance Date is 03/Oct/2011 @ 14h00... and the dates we're check are 03/Oct/2011 @ 15h00 and and 07/Oct/2011 @ 10h00.

At the moment, that would not yield a result because of the time factors. I need to do a between on the date part only... So, between 03/Oct/2011 @ 0h00 and really 08/Oct/2011 @ 0:00.

Is there a neat way to do this?

Edit:

We're developing for a 2008 machine, but we're developing ON 2005 machines. Long story, but I can't use the nice and neat DATE fix.

I am trying the DATEADD me开发者_JAVA百科thod, but am finding an issue.

This is not returning the result I expect:

DECLARE  @Table TABLE
(
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO @Table VALUES ('02-Jan-2011 13:00:00', '07-Jan-2011 15:30:00')

SELECT * FROM @Table
DECLARE @Date DATETIME
SET @Date = '07-Jan-2011 16:00:00'

SELECT
    CASE WHEN 
        @Date BETWEEN DATEADD(dd,DATEDIFF(dd,0,StartDate),0) AND DATEADD(dd, DATEDIFF(dd,0,EndDate),0)-- must cover the appearance date or still open
    THEN 1
    ELSE 0
    END AS Result
FROM @Table

Must I add +1 to the BETWEEN dates to include the last date? 'BETWEEN' doesn't seem to be inclusive of the last date..

You have SQL Server 2008 so just cast to date

SELECT .. 
FROM ... 
WHERE ... AND 
     CAST(ISNULL(ICA.actual_appearance_date, ICA.scheduled_appearance_date) AS date)
        BETWEEN
           CAST(LO.order_start_date AS date)
        AND 
           CAST(ISNULL(LO.actual_expiry_date, LO.expected_expiry_date) AS date)

For SQL Server 2005 and earlier, use the DATEDIFF/DATEADD trick: Best approach to remove time part of datetime in SQL Server

Do not use varchar or float conversions

Edit:, for SQL Server 2005

You need to apply the DATEADD/DATEDIFF to all values

...
SELECT
    CASE WHEN 
         DATEADD(dd,DATEDIFF(dd,0,@Date),0)
              BETWEEN DATEADD(dd,DATEDIFF(dd,0,StartDate),0)
              AND DATEADD(dd, DATEDIFF(dd,0,EndDate),0)
    THEN 1
    ELSE 0
    END AS Result
FROM @Table

Or

WHERE ... AND 
     DATEADD(dd,DATEDIFF(dd,0,ISNULL(ICA.actual_appearance_date, ICA.scheduled_appearance_date),0)
        BETWEEN
           DATEADD(dd,DATEDIFF(dd,0,LO.order_start_date),0)
        AND 
           DATEADD(dd,DATEDIFF(dd,0,ISNULL(LO.actual_expiry_date, LO.expected_expiry_date),0)

You can use the convert process then compare:

Convert(Datetime,Convert(Varchar,OrderDate,106),110)