question about search

[Bill]

Ok the other day I was doing a search of the database using the following statement;

select *
from receiving
where recdate between '11082003' and '11102003'

which should show me only data that is between those dates ... right!

well the problem is I got back not only that data but also date that was from 2001 & 2002. I found a work around by adding a not like statement to receive the data I only want. My question is, is there a setting that I'm missing or is this a bug?

[MAK]

--because it is a varchar field
use tempdb

create table receiving (id int, recdate varchar(8))
insert into receiving select 1, '11082003'
insert into receiving select 2, '11102003'
insert into receiving select 3, '11102002'
insert into receiving select 4, '11092001'

select * from receiving where recdate between '11082003' and '11102003'

--result
id,recdate
1,11082003
2,11102003
3,11102002
4,11092001


drop table receiving

create table receiving (id int, recdate datetime)
insert into receiving select 1, '11/08/2003'
insert into receiving select 2, '11/10/2003'
insert into receiving select 3, '11/10/2002'
insert into receiving select 4, '11/09/2001'

select * from receiving where recdate between '11/08/2003' and '11/10/2003'

--result
id,recdate
1,2003-11-08 00:00:00.000
2,2003-11-10 00:00:00.000

[rmiao]

What's data type of recdate? I guess it's not datetime.

[Bill]

Originally posted by rmiao
What's data type of recdate? I guess it's not datetime.

It's the calendar date ... mmddyyyy

[MAK]

there is not datatype called "calendardate" in sql server.
we have only datetime.
I believe yours is varchar.

do

sp_help receiving

this will display the datatype of the columns.

[MAK]

try this query

select * from receiving where convert(datetime,left(recdate,2)+'/'+substring(recdate,3,2)+'/'+right(recdate,4))
between '11/08/2003' and '11/10/2003'

[Bill]

Originally posted by MAK
there is not datatype called "calendardate" in sql server.
we have only datetime.
I believe yours is varchar.

do

sp_help receiving

this will display the datatype of the columns.

I did this command and found out that's it is a nvarchar.

(sorry about the misunderstanding ... I meant that the data we enter in this field was the calendar date.)

[MAK]

then this query should work

select * from receiving where convert(datetime,left(recdate,2)+'/'+substring(recdate,3,2)+'/'+right(recdate,4))
between '11/08/2003' and '11/10/2003'

[Bill]

yes that query does work ... Thanks again for all your help.