[RESOLVED] Problem dealing with dates using ADO and Access 2000

[Neverbirth]

I'm currently working on a project which is basically a schedule (a bit similar to Outlook Calendar), but I'm having a problems when selecting the events stored in the database.

I have a monthview control, in which I can choose different time spaces, and then display the events for those days... the problem is that I've tried different SQL queries and all of them are giving problems. I've tried using date/time fields and enclosing the dates between ## and without them, and because of some strange reason if I even try to do something simple like "SELECT * FROM tbl_events WHERE event_date > #01/01/2007#" the database returns events before that date. The closest way of getting this going as expected has been using text fields and doing something like "SELECT * FROM tbl_events WHERE event_date BETWEEN '" & date1 & "' AND '" & date2 & "'", but it gives me problems when the first date and the second one are from another month.

Any help would be greatly appreciated. Also, the dates are displayed and stored using "dd/mm/yyyy" format.

[techgnome]

Bottom line.... dates aren't really stored dd/mm/yyyy ... and so if you ask for 01/01/2006 and 07/01/2006 ... it thinks you want Jan 1 through July 1st.... your best bet is to format the dates in the SQL using 1) # around the dates ... 2) put them in yyyy-mm-dd format. It'll cause less problems.....

-tg

[Neverbirth]

Bottom line.... dates aren't really stored dd/mm/yyyy ... and so if you ask for 01/01/2006 and 07/01/2006 ... it thinks you want Jan 1 through July 1st.... your best bet is to format the dates in the SQL using 1) # around the dates ... 2) put them in yyyy-mm-dd format. It'll cause less problems.....

Thanks for your reply.

Well... dd/mm/yyyy is the format used in my country and that's the default method Access and tons of other programs store dates, plus how datepicker and monthview values are displayed.

I've tried doing "SELECT * FROM tbl_events WHERE event_date > #12/04/2006#" to check mm/dd/yyyy and even so it displays an event from 28/11/2006... rather strange.

How could I test this using yyyy-mm-dd format?

EDIT: I see, the format function is enough for this, also, I forgot to convert back the field to date format earlier when I tried mm/dd/yyyy. It now works as it should. Thanks again for your help.

[si_the_geek]

A bit late, but for a little clarification:

When stored in Date fields/variables/properties (in databases/programs), dates are never stored in any particular format - they are actually stored as numbers, and are just formatted appropriately (typically using your computers Short Date format) for display purposes.

No matter what your settings the SQL standards dictate the US format (mm/dd/yyyy), and that is how your dates will be interpreted, unless you have a date like 13/12/2006 which can only be interpreted the way you meant it.

Luckily there is also provision for the format that TG mentioned (which will always be interpreted properly), so try this:
"SELECT * FROM tbl_events WHERE event_date > #2006-12-04#"