Click here to Skip to main content
15,867,308 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hello,

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);


Thanks...
Posted
Comments
[no name] 8-Jul-12 15:58pm    
Have tried replacing the '/' with a '-'?

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
 
Share this answer
 
What about format function?
It would be something like this
SELECT * from Customer WHERE RegDateTime >= FORMAT('02/05/2012', "dd/mm/yyyy")....
 
Share this answer
 
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#
 
Share this answer
 
Comments
saqib.akhter 9-Jul-12 6:40am    
Thanks..
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.
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)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900