Posted by Erland Sommarskog on 05/04/06 00:33

Jim Armstrong (armstrongjc@hotmail.com) writes:
> CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
> CONVERT(VARCHAR(10), @begindate,101) AND
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
> CONVERT(VARCHAR(10), @enddate,101))

But this does not make any sense. When I run

select convert(varchar(10), getdate(), 101)

I get

05/03/2006

So if the user enters 2006-05-03, you will also give him hits from
2005???

If you feel compelled to use string format, use format 112 which is
YYYYMMDD, which is possible to compare, and which also is unambiguosly
convertible back to date.

But it would make more sense to simple say:

O.TradeDate BETWEEN @begindate AND @enddate

Provided that is, that TradeDate always have a timeportion of midnight.
And the same provision applies to @begindate and @endate, but I got
the impression that the user enters date only.

If TradeDate also includes hours and seconds, you need to write:

O.TradeDate >= @begindate AND
O.TradeDate < dateadd(DAY, 1, @enddate)

There is one more important thing to observer here: you should avoid
putting TradeDate in an expression. This is because if there is an
index on TradeDate, that index cannot be used if you put TradeDate in
an expression, and performance will suffer.

> CONVERT(VARCHAR(10),dbo.tblOrders.TradeTime,101) >=
> CONVERT(VARCHAR(10), @begindate,101) AND
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
> CONVERT(VARCHAR(10), @enddate,101)))))

The same applies here, although I'm not really clear what is in TradeTime.
Is TradeDate 2006-05-03 00:00:00 and TradeTime is 2006-05-03 12:23:23?

In such case, I'm not sure why you need to check both.

> CONVERT(VARCHAR(10),dbo.tblOrders.TradeDate,101) >=
> CONVERT(VARCHAR(10), @begindate,101) and
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADEDATE,101) <=
> CONVERT(VARCHAR(10), @enddate,101))
>
> OR (
>
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TradeTime,101) >=
> CONVERT(VARCHAR(10), @begindate,101) AND
> CONVERT(VARCHAR(10),DBO.TBLORDERS.TRADETIME,101) <=
> CONVERT(VARCHAR(10), @enddate,101))))

And then you have it once more?

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

• Next in forum: Re: How to do an equivelent First() operation
• Prev in forum: Re: Problem with using BETWEEN for date range...
• Thread view: Re: Problem with using BETWEEN for date range...

[Reply to this message]