Returning records between 2 dates
-
Hi
I have the following SQL in a stored procedure to return all records with date values between two entered dates:
SELECT RecID, Desc
FROM tblFiles
WHERE CTimeStamp BETWEEN ' + CHAR (39) + CONVERT (VARCHAR, @DateFrom, 101) + CHAR (39) + ' AND ' + CHAR (39) + CONVERT (VARCHAR, @DateTo, 101) + CHAR (39)
Example date values sent to the system would be:
@DateFrom: 'Jul 5 2005 12:00:00:000AM'
@DateTo: 'Jul 20 2005 11:59:59:000PM'
Example CTimeStamp field in the database would be:
Record1: 2005-07-20 20:57:40.000
Record2: 2005-07-19 20:24:33.000
Record3: 2005-07-18 12:33:35.000
In the samples above, Record1 will not be returned. Anyone have any idea where I am going wrong?
Thanks
Brendan
-
Brenden use < and > rather than between it will solve your problem and run faster as well.
[edit added an example showing that you need to use date add to get the correct dates]
declare @dateFrom datetime
declare @dateTo datetime
Set @dateFrom = DateAdd(DD,-1,PassedDateFrom)
Set @dateTo= DateAdd(DD,+1,PassedDateTo)
Select RecID
From tblFiles
Where CTimestamp >@dateFrom
And CTimeStamp <@dateTo
-
FASTER???????? I'd like to see some numbers please.
-
Ok Remi I will dummy up some number for you.
Actually the run faster statement came from on of the big brains and I will find the cite for you. Mike
-
Why are you converting your @DateFrom and @DateTo values? Will this capture record one if you do not convert those?
If you are converting them because the SP has the user type in a date without the timestamp, you may want to redefine the @Variables to contain a timestamp and again, not convert them to 101.
I wasn't born stupid - I had to study.
-
Sorry I thaught you were comparing between and
. -
I thought that too. That would be a great citing to see. Thanks Du Bois!
I wasn't born stupid - I had to study.
-
Forgot to add the actual "boost".
As small as it is, it's because you have one less convert to do (unless the plan converts the date to varchar()).
-
Ah I can not seem to be able to find the cite as the search function is only giving me the last 50 hits. But of interest I did find this on the net.
Short Link: http://www.houseoffusion.com/lists.cfm/link=m:6:613:2139 I ran a test here on a 2 million row table running several hundred iterations per run. I found no statistical significance for using "between" versus "<>" operators. (p=0.626) Indexed or not doesn't seem to matter either. I would bet that most query parsers/planners basically turn the between in a >= <= in the end run, so they're basically the same.
Which would seen to shoot down my argument that <, > is faster. Drat there goes my life long string of never having been wrong.
I thought I was wrong once before only to find out I was misteken. 
Mike
-
BETWEEN is ALWAYS converted to =
-
Between will return different rows than < and >
the between operator is actually >= and <=
I find between problematic, especially when querying for a day. Your example
Set @dateFrom = DateAdd(DD,-1,PassedDateFrom)
Set @dateTo= DateAdd(DD,+1,PassedDateTo)
will actually get records that are exactly on midnight for the next day. or you can modify end date by adding a time of 11:59:59 to the date but this has given me funky results (Sql server likes to round this value up) . Rather I go
Select RecID
From tblFiles
Where CTimestamp >= @dateFrom
And CTimeStamp < @dateTo
Example
set nocount on
Declare @a table (pk int identity, Datefield datetime)
Declare @BeginDate datetime
, @EndDate datetime
insert into @a (datefield)
select 'Mar 7 1999 12:00AM' union
select 'Mar 15 1999 12:00AM' union
select 'Mar 19 1999 12:00AM' union
select 'Mar 23 1999 12:00AM' union
select 'Mar 24 1999 12:00AM'
set @BeginDate = 'Mar 7 1999 12:00AM'
set @EndDate = 'Mar 24 1999 12:00AM'
-- Between
select pk, convert(varchar(24), datefield,100) as datefield
from @a
where datefield between @BeginDate and @EndDate
-- -- Results
pk,datefield
1,Mar 7 1999 12:00AM
2,Mar 15 1999 12:00AM
3,Mar 19 1999 12:00AM
4,Mar 23 1999 12:00AM
5,Mar 24 1999 12:00AM
-- Greater than less than
select pk, convert(varchar(24), datefield,100) as datefield
from @a
where datefield > @BeginDate
and datefield < @EndDate
pk,datefield
2,Mar 15 1999 12:00AM
3,Mar 19 1999 12:00AM
4,Mar 23 1999 12:00AM
-- Just like Between
select pk, convert(varchar(24), datefield,100) as datefield
from @a
where datefield >= @BeginDate
and datefield <= @EndDate
pk,datefield
1,Mar 7 1999 12:00AM
2,Mar 15 1999 12:00AM
3,Mar 19 1999 12:00AM
4,Mar 23 1999 12:00AM
5,Mar 24 1999 12:00AM
-
Wow, that must have taken some time to write
. -
Thanks for all the replies so far, but in reply to the post asking why I am converting, I get the following error "Syntax error converting datetime from character string." when I use the following select statement:
SELECT RecID, Desc
FROM tblFiles
WHERE CTimeStamp BETWEEN '' + @DateFrom + '' AND '' + @DateTo + '''
-
Anything for my peeps
-
Are CTimeStamp and the Variables of type DateTime??
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply