Board index » delphi » Comparing DateTime values

Comparing DateTime values

Hi!

I'm having a little trouble when trying to create a select statement, which
would return me a set of records that have specific date in it. I'm using

select *
from MyTable
where MyDate = '5.7.2001'

and as one would expect, I would get all the records that have matching
date. Instead I get none of the records, because MyDate field also has some
time attached to it (e.g. 12:05:47), so when comparing date values, the
dates don't match and I get no records returned.

How could this be solved, since SQL server databases have no DATE field type
(to store only dates, not times).

HELP!!!! Using D5 (Update Pack 1) on Win2000 Pro and SQL Server 7.0

Thanks!

regards

 

Re:Comparing DateTime values


Bogataj,

in message news:3b4d66a3$1_2@dnews

Quote
> because MyDate field also has some time attached to it (e.g. 12:05:47), so

when > comparing date values, the dates don't match and I get no records
returned.

Quote

how about using this kind of trick instead?
select * from MyTable where MyDate>='5.7.2001' and MyDate<'5.8.2001'

Regards,
/Li Zhengdao

Re:Comparing DateTime values


The same type of thing happens with Oracle.  There are two options.

If there is the equivalent of the "Trunc" function, which effectively
returns the date part of the date time field, you could try

trunc(Mydate) = '5.7.2001'

The problem with this is that Oracle would not use an index (unless you were
using function based indexes) on the MyDate field.  This may not be the case
for SQL server.

The alternative in Oracle (which is probably the same for SQL server) would
be to do something like:

mydate between to_date('5.7.2001 00:00:00', 'd.m.yyyy hh:mi:ss') and
to_date('5.7.2001 23:59:59', 'd.m.yyyy hh:mi:ss')

which would fix it - Oracle would use an index (if it worked out that it
would be a good idea, or you specified it in a hint).  In Oracle, dates
without times are assumed to be mid-day, which is why the problem would
occur in the first place.  Sounds like SQL server is the same.

Sorry if this does not help much.... but I thought I would mention it :o)

Martyn

Quote
"Jure Bogataj" <jure.boga...@cis.si> wrote in message

news:3b4d66a3$1_2@dnews...
Quote
> Hi!

> I'm having a little trouble when trying to create a select statement,
which
> would return me a set of records that have specific date in it. I'm using

> select *
> from MyTable
> where MyDate = '5.7.2001'

Re:Comparing DateTime values


Thank you both!

Other Threads