Selecting Between 2 dates 2

[ohderek]

I have a very large query running joining many table.
I would like to select between 2 dates on a field called "AppStatusDate".

The format appears to be giving me problems as it looks like this:

'30-09-2002 15:04'

Now does anyone know how I can select between say that date and 30th of October?

Any help would be greatly appreciated.

Thanx
D

 

[malleykr]

Try something like this:

select * from myTable a
where  a.appStatusDate between 
       to_date('30-09-2002 15:04','DD-MM-YYYY HH24:MI') 
       and to_date('31-10-2002','DD-MM-YYYY');

Good luck.

 

[ohderek]

Thanx for your help.

My MS Acces 97 though says that 'to_date' is an "undefined function"

Any other advice please?

 

[malleykr]

Doh, my bad. I had the wrong thinking cap on.

Try something like this instead:
SELECT Table3.appStatusDate
FROM Table3
WHERE (((Table3.appStatusDate)>=#30-09-2002 15:04#))
AND (((Table3.appStatusDate)<=#31-10-2002#))

The problem I see is that since your date format is in a different order, the above will only work if the day of the month is >12 (so Access knows it is in reverse order).

 

[lespaul]

Wouldn't the Is Between work like this:

SELECT Table3.appStatusDate
FROM Table3
WHERE Table3.appStatusDate Is between #30-09-2002 15:04#
AND #31-10-2002 23:59#

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[ohderek]

I actually changed yours and I got it to work

WHERE TABLEA_SUMMARY.CAPP_STATUSDATE between #01-09-2002# AND #11-09-2002#

I was looking into such complex queries to do this too.

Thanks a lot for your help

D