Click here to Skip to main content
13,046,679 members (64,505 online)
Rate this:
Please Sign up or sign in to vote.
See more:

I got a problem to execute a date validation query for MS access database under ADO VC++ programming (VC6.0).

I am using the below mentioned query for date validation but it's giving mismatch date format error message because date format in MS Access Database is "DD-MM-YYYY HH:MM:SS"

So please tell me how to make it correct.

Here is the code:

eDBRecordsetPtr->Open("SELECT * from Customer WHERE RegDateTime >= '02/05/2012'  AND RegDateTime < '10/05/2012' ", eDBConnectionPtr.GetInterfacePtr(), adOpenForwardOnly, adLockReadOnly, adCmdText);

Posted 8-Jul-12 9:56am
Wes Aday 8-Jul-12 15:58pm
Have tried replacing the '/' with a '-'?
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

I think you can change the format according to ISO standard. and for control DateTime_SetFormat macro is used for c/c++. try it out see if you can set your format
Rate this: bad
Please Sign up or sign in to vote.

Solution 3

What about format function?
It would be something like this
SELECT * from Customer WHERE RegDateTime >= FORMAT('02/05/2012', "dd/mm/yyyy")....
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

You should use the english date format 'mm/dd/yyyy' and enclose the date specifications by hash characters.

With ranges, use the BETWEEN operator and include the times:
SELECT * from Customer WHERE RegDateTime BETWEEN #02/15/2012 00:00:00#  AND  #02/25/2012 23:59:59#

If you want to limit the query to a single day, use the LIKE operator:
SELECT * from Customer WHERE RegDateTime LIKE #02/15/2012#
saqib.akhter 9-Jul-12 6:40am
It's working fine and I also have one more questions for converting the CSting to above date format.
Jochen Arndt 9-Jul-12 6:54am
You may use the COleDateTime class. The member function ParseDateTime() can be used to convert a datetime string to a date object (COleDateTime uses internally the DATE type which stores the date in a double and is also the format used by Access).

To create the query string use CString::Format() passing the date elements or COleDateTime::Format() passing a format string or MAKELCID(LANGID_ENGLISH_US, SORT_DEFAULT) as LCID.
armagedescu 9-Jul-12 16:52pm
In some generic case using english format is ok. But in a heterogeneous environment, when you bind dates to some external variables, this is not the answer. And this is definitely not the answer in a nonenglish environment.
Using like function makes string bypattern comparison, so it converts both, right and left operands to string. In fact when working with dates you have to avoid as much as possible string comparisons. To make a date from a string use Format function, with specifying the exactly provided format. To compare a day date to a day date, you should use datepart/datediff. In MS Access SQL you may use VBA functions. Both, builtin and your own. See documentation on VBA datetime functions.
Jochen Arndt 10-Jul-12 3:32am
I suggested the english format because I remember a Microsoft document stating that this format is always understood regardless of the localization setting. With newer Access version, the ISO 8601 format can be also used.

You are right that using strings for dates is not the best solution. My intention was to point out the syntax errors.

The best solution would be to use a query with parameters where the date can be passed by binary value.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web02 | 2.8.170713.1 | Last Updated 9 Jul 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100