I have a problem in this query:

select order from ordertable
where orderdate >= '06/20/2007' and orderdate < '06/21/2007'

this query do not return any record
although there are records in the table matching the query

please help me

I would expect this query to work if the orderdate column is a datetime data
type (although format 'yyyymmdd' is preferred since it is unambiguous). In
the case of char or varchar, string comparison rules are used instead so you
might not get the results you expect.

--
Hope this helps.

Dan Guzman
SQL Server MVP

<navneetkasulkar (AT) gmail (DOT) com> wrote

OK, I guess you noticed that you used the same date for both.

What is the datatype of orderdate?

Roy Harvey
Beacon Falls, CT

On Tue, 26 Jun 2007 10:42:30 -0000, "navneetkasulkar (AT) gmail (DOT) com"
<navneetkasulkar (AT) gmail (DOT) com> wrote:

Looking at ur dates the 2nd part of the and >= should and will only have
items
before 06/21 because time is involved so 06/21/2007 is as of midnite on the
20th
you mite wanna make 06/21/2007 to 06/22/2007 and you should get everything
upto midnite
on the 21st
MJ

<navneetkasulkar (AT) gmail (DOT) com> wrote

On Jun 26, 3:42 am, "navneetkasul... (AT) gmail (DOT) com"
<navneetkasul... (AT) gmail (DOT) com> wrote:
1. Make sure that the data is set to datetime or smalldatetime
2. Select order from ordertable
where orderdate >= '2007-06-20' and orderdate < '2007-06-21'

or to change the date format with:
SET DATEFORMAT mdy;
then do the query.

See: http://www.sqlhacks.com/index.php/Dates/SearchForDates
for details and explanations

Also new this week:

How to search date fields in Microsoft SQL Server
How to deal with quotes in literals with Microsoft SQL Server
How does case sensitiveness affect searches on Microsoft SQL Server
Effect of concatenation on Microsoft SQL Server
Which operator to use for better performance when using Microsoft SQL
Server
How to improve the union queries on Microsoft SQL Server
How many duplicates do I have in the database on an MS SQL Server
How to summarize data with Microsoft SQL Server
How to optimize Microsoft SQL Server
How to retrieve data with Microsoft SQL Server
How to drop the time portion of a DateTime column in MS SQL Server
How to get both the details and the subtotals with Microsoft SQL
Server
How to calculate grand totals with SQL Server with the GROUP BY WITH
ROLLUP

On Jun 26, 3:42 pm, "navneetkasul... (AT) gmail (DOT) com"
<navneetkasul... (AT) gmail (DOT) com> wrote:
Hi friends

Thanks for your reply

the problem is solved
i change the datatype of the column to 'datetime' from 'varchar'
and it works
thanx once again
and have a nice day