Between for date range does not fetch all records

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Between for date range does not fetch all records

Author

Topic

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-07-03 : 04:06:59

When I use between for the date range selection, the values for the end date is not picked up.


Select col1,col2,dtcol3 from testtable where dtcol3
between '20060601' and '20060630'

The above query doesnt pick data for the records where dtcol3 has value like 2006-06-30 10:04:30.123 or 2006-06-30 08:38:04.670, anything which has the date 2006-06-30. All other records between 2006-06-01 and 2006-06-29 is fetched, only 30th records are not picked.

Interestingly this query works.


Select col1,col2,dtcol3 from testtable where
convert(varchar(8),dtcol3,112) between '20060601' and '20060630'

This fetches all records between dates 01 and 30 for 06 month.

I dont understand why the first query is failing to pick all records...

Perhaps, somebody can explain what Iam missing there...

Thanks

Karunakaran

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-03 : 04:10:08

because the 1st query retrieves until 20060630 00:00:00

--------------------
keeping it simple...

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-07-03 : 04:18:43

but it should be till end of that date right?
20060630 23:59:59?

Karunakaran

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 04:21:20

Looks like your column dtcol3 contains date & time.

"dtcol3 between '20060601' and '20060630'"
This only return records where dtcol3 up to midnight 2006-06-30. So records after midnight 2006-06-30 are not included

"convert(varchar(8),dtcol3,112) between '20060601' and '20060630'"
Here, you have converted dtcol3 to string of format YYYYMMDD and the time component is excluded in the conversion. That's why those records are also included.

For best performance you should use

dtcol3 >= '20060601' 
and    <  '20060701'

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-07-03 : 04:22:03

if you don't specify a time, default is 00:00:00

try this...

declare @d1 datetime
set @d1='20060612'
select @d1

--------------------
keeping it simple...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 04:26:11

You should omit time portion from date

Where date>=DateAdd(day,DateDiff(day,0,date1),0)
and date<DateAdd(day,DateDiff(day,0,date2),1)

And this will make use of index if it exists

Madhivanan

Failing to plan is Planning to fail

karuna
Aged Yak Warrior

582 Posts

Posted - 2006-07-03 : 04:54:49

Now talking about performace which is best?

Where date>=DateAdd(day,DateDiff(day,0,date1),0)
and date<DateAdd(day,DateDiff(day,0,date2),1)

or

dtcol3 >= '20060601'
and < '20060701'

or

convert(varchar(8),dtcol3,112) between '20060601' and '20060630'

Since mine and Maddys is using additional conversion / functions, I think khtans should be best... Any disagreements?

Karunakaran

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 05:01:12

>>Since mine and Maddys is using additional conversion / functions, I think khtans should be best... Any disagreements?

Set the execution plan and see.

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-03 : 05:19:19

actually the 1st query and 2nd query are the same. The only different is the value is hardcoded in the 2nd query whereas the 1st one uses variable.

Where dtcol3 >= DateAdd(day,DateDiff(day,0,@date1),0)
and dtcol3 < DateAdd(day,DateDiff(day,0,@date2),1)

dtcol3 >= '20060601'
and dtcol3 < '20060701'

manohar
Starting Member

6 Posts

Posted - 2006-07-03 : 07:42:33

Try this

dtcol3 between
convert(datetime, '20060601' +' '+ '00:00:00.000 ',101) and
convert(datetime,'20060630'+' '+ ' 23:59:59.997 ',101)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-07-03 : 08:51:49

quote:
Originally posted by manohar

Try this

dtcol3 between
convert(datetime, '20060601' +' '+ '00:00:00.000 ',101) and
convert(datetime,'20060630'+' '+ ' 23:59:59.997 ',101)

I hope you have read previous replies

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources