Limiting date range with precision in MS SQL / SQL Server 2005
I want to restrict the report to return records from date A to Date B. This is what I was 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:00 am January 1st to 23:59 pm March 31st (this completeness is the default when no time is specified). But I noticed an inconsistency that there is a time 00:00:00 in the record that it will be part of this set.
Is there a more accurate way to do this so that it accurately returns the date range that I want? *
I've 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 awkward: SQL Server will ROUND hundresha seconds in half. So I get the April 1st record (ha! April Fool record! Grr) if I use any time later than 11:59:29. Why is this?
- (I'm sure there is. I'm new to this. Thanks for your help!)
source to share
There is always an 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
source to share
I suspect the date column in view_Inspections is the SmallDateTime datatype. This data type has an accuracy of 1 minute, which explains your unexpected results (rounding seconds to the nearest minute).
The Roland Shaw Method offers the best way to modify your request to suit your needs.
source to share
The BETWEEN operator is included, so you see the results you are in your first query. The rounding you see in the second query will depend on what kind of date and time data type is used in your table. (By the way, I think you are knocking seconds off with hundredths of a second). It looks like you are probably using smalldatetime on your table, in which case the time is rounded to the nearest minute.
If your table uses date and time, try explicitly converting @startDate and @endDate to DATETIME (CAST (@endDate AS DATETIME)).
Quick note ... even for DATETIME values, SQL Server only runs from 3 / 100th of a second, so 11: 59: 59.999 rounds to 12: 00: 00.000.
You basically have three options:
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 is not very intuitive and is error prone in my opinion. The second way kills performance as indexes cannot be used and it becomes much more complicated if you can run searches that span years or start / end in the middle of months. The third method that Rowland suggested is the best I think.
source to share
Just 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 to include 04/01
, change the end date to 03/31/2008
.
source to share