Limiting a date range with exactness in MS SQL / SQL Server 2005

Question

I want to limit a report to return records from Date A through Date B. This is what I have been doing:

declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008'
set @endDate = '04/01/2008'
-- test what are the start and end dates
select min(date),max(date) from view_Inspections 
where date between @startDate and @endDate

... which I was told returned records from 12 am Jan 1st through 11:59 pm March 31st (that midnight is the default when no time is indicated). But I noticed a discrepancy, which is if a record has a time of 00:00:00 that it will be part of this set.

Is there a more exact way of doing this so it will return exactly the date range I want?*

I tried using time:

declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008 00:00:00'
set @endDate = '04/01/2008 11:59:59'
-- test what are the start and end dates
select min(date),max(date) from view_Inspections 
where date between @startDate and @endDate

... but I noticed something wonky: SQL Server will ROUND the hundreth-second up by half. So I get that April 1st record (ha! April Fool's record! grr) if I use any time later than 11:59:29. Why is that?

• (I feel sure there is. I'm new at this. Thanks for your help!)

Answer 1

There's always the easy option:

declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008'
set @endDate = '04/01/2008'

-- test what are the start and end dates
select min(date),max(date) from view_Inspections 
where date >= @startDate 
and   date < @endDate

Answer 2

I suspect that the date column in view_Inspections is a SmallDateTime data type. This data type has 1 minute accuracy, which explains your unexpected results (rounding the seconds to the nearest minute).

The method Roland Shaw suggests is the best way to modify your query to accommodate your requirements.

Answer 3

The BETWEEN operator is inclusive, which is why you're seeing the results that you are in your first query. The rounding that you're seeing in your second query is going to be dependent on what exact datetime data type you are using in your table. (BTW, I think you're confusing seconds with hundredths of seconds). It looks like you're probably using a smalldatetime in your table, in which case the time is rounded to the nearest minute.

If your table is using datetime, try explicitly converting your @startDate and @endDate to DATETIME values (CAST(@endDate AS DATETIME)).

A quick note... even for DATETIME values, SQL Server is only accurate to the 3/100ths of a second, so 11:59:59.999 will get rounded up to 12:00:00.000.

You basically have three choices:

1) BETWEEN CAST('01/01/2008 00:00:00.000' AS DATETIME) AND CAST('03/31/2008 12:59:59.997' AS DATETIME)

2) YEAR(my_date) = 2008 AND MONTH(my_date) BETWEEN 1 AND 3

3) my_date >= CAST('01/01/2008 00:00:00.000' AS DATETIME) AND my_date < CAST('04/01/2008 00:00:00.000' AS DATETIME)

The first method isn't very intuitive and is error-prone in my opinion. The second method kills performance since indexes can't be used and it becomes much more complex if you can have searches that span years or begin/end in the middle of months. The third method, which Rowland suggested, is the best I think.

Answer 4

Simply try removing the time from the date field like so:

declare @startDate varchar(20)
declare @endDate varchar(20)
set @startDate = '01/01/2008'
set @endDate = '04/01/2008'

SELECT min(date),max(date) FROM view_Inspections 
WHERE CAST(FLOOR(CAST(date AS FLOAT)) AS DATETIME) BETWEEN CAST(@startDate AS DATETIME) And CAST(@startDate AS DATETIME))

This will return everything from 01/01/2008 00:00:00 to 04/01/2008 11:59:59.999. If you don't want 04/01 included, change your end date to 03/31/2008.

Answer 5

Your best solution is just create a BIGINT(10) field that called "julian", and store it in YYYYMMDD.

Then do the query where julian >= '20120103' AND julian <= '20120203'