>Hi,
>
>Thank a lot for your advise.
>Actually, most of my query will need to
>query the datetime column as part of the
>condition using this method, where convert(varchar(10),exp_dt,21) = '9999/12/31'.
>

SQL cannot optimize a CONVERT() or CAST() expression.

>Using QA, I found that even for my largest table, around 13 Millions
>records, SQL still using clustered index scan. I am thinking to
>help SQL server use index seek. Hence, I was thinking whether
>converting all the datetime column to a numeric value
>and vice versa, index that column as part of my search
>condition, then it should be faster.
>

Because SQL use various statistic measures to compute the cost of every plain of execution as,
how much are they the rows ( pages ! ) that satisfy the condition?
Probably the condition is true for many millions of rows,
and SQL it holds more convenient to read all the pages of the table respect
to increase the computation time without a great pages selectivity.

Remembered, in SQL the cost is determined by the page, not from the row,
then with a little row, you can read 30% of the rows and this require a 100% of pages read.

>As for your method of using exp_dt = '99991231',
>does it take into my column of the time value ?,
>since some of my datetime column might have time
>value, some might not have, will it affect my
>query.
>

SQL can optimize datediff(), then you can search a date within a datetime:

select exp_dt from pd_security where DATEDIFF(d,exp_dt,'99991231')=0

>Any best way to solve my issue.
>
>Please advise.