Inserting a date into MS Jet Database

[ahmd]

Hi everyone:

I'm getting conflicting results from my search, so I'd appreciate if someone can clear this out for me. I need to insert a date & time value from COleDateTime variable into a Microsoft Jet Database. I use the following SQL query for that:

INSERT INTO `table name` (`date field name`)  VALUES ('#yyyy-mm-dd hh:mm:ss#')

I can't seem to find a consensus whether it should be dash or a slash, or whether it should start with a year or a month? I obviously want to code this independent of a user's locale.

And second question, say if COleDateTime is not valid, how do I form the SQL date in that case?

[Arjay]

What are you using to connect to MS Access?

[ahmd]

What are you using to connect to MS Access?

CDao* classes from an MFC project.

[VictorN]

One of my friends, Microsoft Access MVP, said me last year that Microsoft Access & Jet works very good with the datetime values passed in US format. And it should work for every used locale.

[ahmd]

OK, I got most of it to work. Here's my findings in case someone has a similar situation.

First let me tell you that MS again decided to stand out from the crowd and stepped away from a conventional SQL standard in their MS Jet engine. Here's what I mean:

1. The date value should not be included in ' ' like one would do for string literals. Instead one should enclose it into # # only, which I'm sure stems from the VB conventions.

2. I'm still not clear how to format the date. All of the following formats seems to work, and I can't find a consensus on which one should be used as a norm. And unfortunately I cannot test it on any locale other than the US one.

#mm/dd/yyyy#
#yyyy/mm/dd#
#yyyy-mm-dd#

Example of SQL statement:

INSERT INTO `my table` (`my date`) VALUES (#2011-03-20#)

3. The MS Jet's SQL DATE parameter can be represented by either only date part, only time part or as both date and time. Again, the exact formatting is not clear to me. It also seems like all the dates are stored as text in the database so maybe there's such a variation.

4. To store an invalid date (for instance from the COleDateTime member) simply assign it to Null (without # # delimiters). Example of SQL statement:

INSERT INTO `my table` (`my date`) VALUES (Null)

[Arjay]

OK, I got most of it to work. Here's my findings in case someone has a similar situation.

First let me tell you that MS again decided to stand out from the crowd and stepped away from a conventional SQL standard in their MS Jet engine.

I've never had any problems saving or reading dates in Access. Then again, I'm not using obsolete and deprecated technology like CDao* classes.

Use current technologies and reduce the risk of problems - it's funny how that works.

[VictorN]

...
And unfortunately I cannot test it on any locale other than the US one.

#mm/dd/yyyy#
#yyyy/mm/dd#
#yyyy-mm-dd#

Well, the #mm/dd/yyyy# must work (according to the information I got from MS Access MVP)
The #yyyy-mm-dd# must work too because it is the locale independable format for dates.
I can say nothing about the #yyyy/mm/dd# format, sorry.

For Access 97/2000 (and I guess for XP/2003 also) you can pass as a datetime the double value representing datetime in COleDatetime class (just because the datetime representation in MS Access and in COleDatetime was the same)
I don't know how it was realized in 2007/2010 thogh.