asked
need logic
The table structure is like this ... eid fromdate todate ---- -------- ------ 1 03/07/2011 03/11/2011 DECLARE @FROMDATE DATETIME,@TODATE DATETIME SET @FROMDATE='03/08/2011' SET @TODATE='03/11/2011' SELECT * FROM EMPLOYEE WHERE (CONVERT(VARCHAR,FROM_DATE,112) BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) OR (CONVERT(VARCHAR,TO_DATE,112) BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) in the above example...it is returning the data if i give from date as '03/08/2011'..because there is a value in the database...suppose if i give ... DECLARE @FROMDATE DATETIME,@TODATE DATETIME SET @FROMDATE='03/08/2011' SET @TODATE='03/10/2011' SELECT * FROM EMPLOYEE WHERE (CONVERT(VARCHAR,FROM_DATE,112) BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) OR (CONVERT(VARCHAR,TO_DATE,112) BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) from the above query ...i am not getting any records...
You shouldn't use functions on the columns in the where clause. If you use any function on a column in the where clause you don't have a searchable argument and index seeks will be out of the question. -- This syntax can't use an index SELECT * FROM EMPLOYEE WHERE (CONVERT(VARCHAR,FROM_DATE,112) BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) OR (CONVERT(VARCHAR,TO_DATE,112) BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) --but this can SELECT * FROM EMPLOYEE WHERE FROM_DATE BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) OR TO_DATE BETWEEN CONVERT(VARCHAR,@FROMDATE,112) AND CONVERT(VARCHAR,@TODATE,112)) The problem with your statement is that in the first case from_date is not between your @fromdate ('03/08/2011') and @todate('03/11/2011'), but your to_date is. In the second statement, none of from_date and to_date is between the parameters. Convert(varchar(10),somedate,112) will give you the date in this format yyyymmdd, but why do you want to convert the dates to a string? A string comparison is slower than a date compare. Also keep in mind that the default size of varchar in a convert function is 30 bytes, but in a declare statement it's only 1 byte. It's a good recommendation to always specify optional parameters.
@Håkan Winther You make a very good point that there is not a need to convert the dates to string. This conversion is an extremely evil thing to do and never, ever makes any sense. Additionally, it might create a problem simply because the semantics of datetime and strings comparisons are quite different. So, the way to do it right should be:
select *
from Employee
where
(FROM_DATE between @FROMDATE and @TODATE)
or (TO_DATE between @FROMDATE and @TODATE);
Yup - SQL wants the RAW stuff... limit the manipulations and let it do its job.
You may need to keep the formatting of the parameters to varchar if the data is stored in varchar (but that would be a design issue), otherwise you end up with implicit conversion that is even more evil, because you can't see the conversion in your statement. You can see it in the execution plan, but...
