Posted by ctotos on 06/27/07 00:53

On Jun 26, 3:42 am, "navneetkasul...@gmail.com"
<navneetkasul...@gmail.com> wrote:
> 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

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

[Back to original message]