Board index » delphi » Querying dates in ADO/Access/Jet 4.0

info.fms

Delphi Developer
Tue, 10 Aug 2004 03:13:32 GMT

Querying dates in ADO/Access/Jet 4.0

I'm using a TADOQuery but can't seem to query against dates (no time). Can
somewhere show us some SQL? Doing ...

SELECT * FROM THETABLE WHERE THEDATE  = :PDATE

doesn't seem to bring back anything.  The pDate has no time part.  How can I
truncate the THEDATE field so it ignores the time, could this be the answer
or do I need to use an SQL function of some sort?

 
accma

Delphi Developer
Tue, 10 Aug 2004 05:00:38 GMT

Re:Querying dates in ADO/Access/Jet 4.0


wrap your date in '#'   ex. #01/01/02#
Quote
"info.fms" <info....@ic24.net> wrote in message news:3c754715_1@dnews...
> I'm using a TADOQuery but can't seem to query against dates (no time). Can
> somewhere show us some SQL? Doing ...

> SELECT * FROM THETABLE WHERE THEDATE  = :PDATE

> doesn't seem to bring back anything.  The pDate has no time part.  How can
I
> truncate the THEDATE field so it ignores the time, could this be the
answer
> or do I need to use an SQL function of some sort?

info.fm

Delphi Developer
Tue, 10 Aug 2004 06:12:30 GMT

Re:Querying dates in ADO/Access/Jet 4.0


This doesn't seem to work. I get no records returned. I've tried the same
thing with a TADOTable by filling in the Filter property and switching the
Filtered property on. The queries and filters (for TADOTable) do work on
other data types.

Quote
> wrap your date in '#'   ex. #01/01/02#
> > I'm using a TADOQuery but can't seem to query against dates (no time).
Can
> > somewhere show us some SQL? Doing ...

> > SELECT * FROM THETABLE WHERE THEDATE  = :PDATE

> > doesn't seem to bring back anything.  The pDate has no time part.  How
can
> I
> > truncate the THEDATE field so it ignores the time, could this be the
> answer
> > or do I need to use an SQL function of some sort?

DRS

Delphi Developer
Tue, 10 Aug 2004 06:41:14 GMT

Re:Querying dates in ADO/Access/Jet 4.0


Quote
"info.fms" <info....@ic24.net> wrote in message news:3c757106_2@dnews...

| > wrap your date in '#'   ex. #01/01/02#

| This doesn't seem to work. I get no records returned. I've tried the same
| thing with a TADOTable by filling in the Filter property and switching the
| Filtered property on. The queries and filters (for TADOTable) do work on
| other data types.

You need to do it anyway because that is how you tell SQL that you are
specifying dates.

--

Quidquid latine dictum sit, altum viditur.
#319

info.fm

Delphi Developer
Tue, 10 Aug 2004 08:24:03 GMT

Re:Querying dates in ADO/Access/Jet 4.0


Got it to work! but ...
1) the date needs to specified in US format mm/dd/yyyy
2) need the # chars to wrap date (Thanks guys)
3) to filter to a specific date (without time) need to write a

[DATE]>=#mydate# AND [DATE]<=#mydate+1#

UNLESS you guys know of a SQL function to trim off the time part you need to
do this.
Actually wonder if TRIM would do it?

4) when specifying multiple comparisons need to ALSO wrap the field (i.e.
DATE in this case) with square brackets i.e. [DATE]

 Thanks everyone for the help. Pheew. ;~)

Quote
"DRS" <d...@removethis.ihug.com.au> wrote in message news:3c75775c_2@dnews...
> "info.fms" <info....@ic24.net> wrote in message news:3c757106_2@dnews...
> | > wrap your date in '#'   ex. #01/01/02#

> | This doesn't seem to work. I get no records returned. I've tried the
same
> | thing with a TADOTable by filling in the Filter property and switching
the
> | Filtered property on. The queries and filters (for TADOTable) do work on
> | other data types.

> You need to do it anyway because that is how you tell SQL that you are
> specifying dates.

> --

> Quidquid latine dictum sit, altum viditur.
> #319

Brian Bushay Team

Delphi Developer
Tue, 10 Aug 2004 11:44:53 GMT

Re:Querying dates in ADO/Access/Jet 4.0


Quote
>I'm using a TADOQuery but can't seem to query against dates (no time). Can
>somewhere show us some SQL? Doing ...

>SELECT * FROM THETABLE WHERE THEDATE  = :PDATE

>doesn't seem to bring back anything.  The pDate has no time part.  How can I
>truncate the THEDATE field so it ignores the time, could this be the answer
>or do I need to use an SQL function of some sort?

Do the date fields in your table show a time component?
--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Other Threads

1. ADO Jet 4.0 & Access Db with password

2. Record locking with ADO - Delphi 5 and Access 97/2000 - Jet 4.0

3. ADO, JET OLEDB 4.0 AND ODBC

4. Installing MS Jet 4.0 OLE DB for ADO use

5. How to seek (ADO->JET 4.0)

6. ADO with JET 4.0 too slow...

7. Access to Access tables, JET and ADO in the future

8. Fast Access To MS Access 200 w/o Jet Engine In ADO

9. Access 2000 on NT 4 using Jet 4.0

10. Connection Failure on XP with Jet 4.0, Access