Please help: date-only comparison

We have a select query on SQL server 2K with some date criteria like this:
transaction_date + 30 <= end_date
Because the data type of the date fields is "Datetime", the comparison gets
to time. This may cause missing records when (transaction_date + 30 =
end_date).
So I tried with Date-only as below:
convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
end_date)
or
convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
end_date, 120)
But this does not work! Please advise on how to truncate TIME in this case.
Thanks!
JohnGet calculations out of one side.
WHERE transaction_date < (convert(smalldatetime, convert(char(8), end_date,
112)) - 29)
If you give some table structure, sample data, and desired results
(including rows that make the solution to "does not work", whatever that
means), we can give a more authoritative answer. Please read
http://www.aspfaq.com/5006 to see why it is important to include more
specific, well, specs.
"John61" <wanghaodong@.yahoo.com> wrote in message
news:uQ4dNkscGHA.4224@.TK2MSFTNGP04.phx.gbl...
> We have a select query on SQL server 2K with some date criteria like this:
> transaction_date + 30 <= end_date
> Because the data type of the date fields is "Datetime", the comparison
> gets to time. This may cause missing records when (transaction_date + 30 =
> end_date).
> So I tried with Date-only as below:
> convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
> end_date)
> or
> convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
> end_date, 120)
> But this does not work! Please advise on how to truncate TIME in this
> case.
> Thanks!
> John
>
>|||Well I don't see a problem.
If you exactly knowthe row which is is is missing or erronously displayed,
then you can do a check on it and see why its getting displayed.
Like
select convert(varchar(10), transaction_date + 30, 120) ,convert(varchar(10)
,
end_date, 120), <other columns>
from all tables
where
conditions
--comment the following condition and check the result set. Maybe that will
give you an insight.
--and convert(varchar(10), transaction_date + 30, 120) <=
convert(varchar(10),
end_date, 120)
Hope this helps.
"John61" wrote:

> We have a select query on SQL server 2K with some date criteria like this:
> transaction_date + 30 <= end_date
> Because the data type of the date fields is "Datetime", the comparison get
s
> to time. This may cause missing records when (transaction_date + 30 =
> end_date).
> So I tried with Date-only as below:
> convert(varchar(10), transaction_date + 30) <= convert(varchar(10),
> end_date)
> or
> convert(varchar(10), transaction_date + 30, 120) <= convert(varchar(10),
> end_date, 120)
> But this does not work! Please advise on how to truncate TIME in this case
.
> Thanks!
> John
>
>