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

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?

 

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?

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?

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

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

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