Inserting a date into MS Jet Database

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:

Code:

---

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?

What are you using to connect to MS Access?

Quote:

---

Originally Posted by Arjay

What are you using to connect to MS Access?

---

CDao* classes from an MFC project.

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.

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.

Code:

---

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

---

Example of SQL statement:

Code:

---

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:

Code:

---

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

---

Quote:

---

Originally Posted by 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.

---

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.

Arjay, it has nothing to do with a class wrapper that connects to the database engine but with the engine itself. I'm making those SQL statements via MFC that has nothing to do with it either.

I know you don't realize this, but in the ATL approach I mentioned, you don't form the sql directly, the consumer classes do it for you.

Sure underneath the covers, t-sql ultimately gets created but you don't need to do it directly.

Now the cool thing is that with MFC and ATL, if you don't know something, you can step into the code and find out how it works - so you can actually see how ATL forms insert statements for datetime fields.

It's pretty neat.

Maybe. But it's a simple timing issue for me -- how long will it take to learn ATL vs. how long will it take to learn MS way to code SQL statements.

Quote:

---

Originally Posted by ahmd

Maybe. But it's a simple timing issue for me -- how long will it take to learn ATL vs. how long will it take to learn MS way to code SQL statements.

---

Ask yourself what you have in the end. When these sorts of things come up, I always opt for moving to new technology and would welcome the small learning curve because I know that having the newer technologies under my belt will make things easier in the future.

Often times, after I explain the benefits to moving to newer technology, my customer will agree because they understand that current code will be easier to maintain moving forward.

Old code becomes harder and harder to maintain (as you know) because as time passes, the experts in that technology are harder to find.

I guess it must be difficult for someone to understand this unless they've been through a few of these, learn new technology/refactor cycles. In hindsight, it's always easy to go back and remember how much harder is was to accomplish things in older technologies. Of course, someone that never moves to new technologies, never realizes this.

Arjay you might be right. You see I'm not coding this app from scratch, in which case I would probably do it as you said. I have to fix an already existing code where the initial programmer didn't escape SQL statements, thus, for instance, when some was using an apostrophe in their name it would throw the whole SQL request off. So I did the escaping, but then there was an issue with the way they were formatting dates and time (namely when the date was invalid). Initially there was no issue with the format of the date, I just happened to look at it and said, "wait a sec. Why is it formatted for US standard? What would happen if the code is run on a non-US locale?" So, anyway, that is the origin of my initial question.

I also need to mention that the software I do this for is a fully operational project that I need to impact with the least amount of changes. So as you can imagine, introducing ATL concept into it would put me into a "lot of trouble" down the road. (Plus I'm sure the customer doesn't want to get a huge bill for redoing the whole "guts" of the system.)

Lastly, you mentioned new things. I'm totally with you on this one. And I did move on to something new. No offense to Windows guys, but I don't program with it that much these days. I moved on to OS X and iOS that I find much more rewarding and way easier to learn and program. It is also more rewarding (income wise) since that platform seems like an untapped resource with a lot of public interest toward it. I kinda feel sad for Windows though, because that is where I started from and I hope it doesn't go down in the years to come. Right now, the outlook doesn't seem that good with Microsoft not really investing much into Windows and mostly expanding their efforts to a million of side projects, and on various charities in case of the owners. Well, I didn't want to go that much off topic here ...

Quote:

---

Originally Posted by ahmd

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

Code:

---

#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.

Quote:

---

Originally Posted by VictorN

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 appreciate your input, Victor.