Checking Date part only, of a DateTime field

Question

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 method, 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..

score 2 · Accepted Answer · edited May 23 '17 at 10:34

2

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)

edited May 23 '17 at 10:34

Community

1
1

answered Oct 10 '11 at 05:33

gbn

422,506
82
585
676

This looks a lot safer than the VARCHAR fix. And because we're using inbuilt data types - SQL won't care if it's DMY or MDY, right? – Craig Oct 10 '11 at 06:14
Also, am I right in saying that because we're using BETWEEN, the 2nd date should have 1 day added to it? So, the "AND CAST(ISNULL(LO.actual_expiry_date, LO.expected_expiry_date) AS date)" should have 1 day added, or else it won't include that day? – Craig Oct 10 '11 at 06:17
@cdotlister: correct, we stcik with native date?time formats so language etc is irrelevant. You don't need to add a day because BETWEEN is inclusive (`>=` and `<=`). So "07 Oct 2011 14:00" will match to "07 Oct 2011 11:00" because both change to "07 Oct 2011". Even though a straight comparison *with* time will be false – gbn Oct 10 '11 at 06:29
I have had to modify my initial post, due to a problem with regards 2008/2005. Basically, we have a crazy situation where we're coding for a 2008 server in production and UAT - but all our dev servers are ... 2005! I know - upgrade them! But this is government, and things go slow... so I can't use DATE. Hope you can assist. – Craig Oct 11 '11 at 00:08
@cdotlister: You need to run the dateadd/datediff on all values. My orginal answer has CAST on all values but your update didn't. – gbn Oct 11 '11 at 05:05

score -2 · Answer 2 · edited Oct 10 '11 at 05:50

-2

You can use the convert process then compare:

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

edited Oct 10 '11 at 05:50

Lance Roberts

22,383
32
112
130

answered Oct 10 '11 at 05:21

shamim

6,640
20
85
151

Checking Date part only, of a DateTime field

2 Answers2