Question about datetime field

Dear all,
I am quite confused when dealing with the date and time in SQL tables.
e.g. I have a table myTable and there is a field myDate. The type of myDate
is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
in SQL Query Analyzer.
However, if I want to choose the records by the following SQL statement:
select * from myTable where myDate < getdate() - 1
Current date is Oct 25, therefore getdate() - 1 should retrieve the records
with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But it
also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know the
time factor also counts. But I don't want this to be counted. I am quite
confused. Any suggestion to get rid of the time factor?
Thanks a million.
Ivan
One method to remove the time portion is with CONVERT. For example:
SELECT *
FROM myTable
WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
Hope this helps.
Dan Guzman
SQL Server MVP
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
> Dear all,
> I am quite confused when dealing with the date and time in SQL tables.
> e.g. I have a table myTable and there is a field myDate. The type of
> myDate
> is "datetime". It shows the date as 2005-10-25 00:00:00.000, for example,
> in SQL Query Analyzer.
> However, if I want to choose the records by the following SQL statement:
> select * from myTable where myDate < getdate() - 1
> Current date is Oct 25, therefore getdate() - 1 should retrieve the
> records
> with myDate up to Oct 23. (Since myDate should be smaller than Oct 24) But
> it
> also retrieves the records with myDate = 2005-10-24 00:00:00.000 . I know
> the
> time factor also counts. But I don't want this to be counted. I am quite
> confused. Any suggestion to get rid of the time factor?
> Thanks a million.
> Ivan
|||Dear Dan,
Thanks for your help! It's very helpful.
Ivan
"Dan Guzman" wrote:

> One method to remove the time portion is with CONVERT. For example:
> SELECT *
> FROM myTable
> WHERE myDate < CONVERT(char(8), GETDATE() - 1, 112)
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Ivan" <Ivan@.discussions.microsoft.com> wrote in message
> news:6026FA8F-6B3A-4975-90F6-894FD65FD844@.microsoft.com...
>
>