-
question about search
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?
-
--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
-
What's data type of recdate? I guess it's not datetime.
-
Originally posted by rmiao
What's data type of recdate? I guess it's not datetime.
It's the calendar date ... mmddyyyy
-
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.
-
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'
-
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.)
-
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'
-
yes that query does work ... Thanks again for all your help.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|