Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VC6 C++ ADO
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 8-Jul-12 10:56am
Comments
Wes Aday at 8-Jul-12 15:58pm
   
Have tried replacing the '/' with a '-'?
Rate this: bad
good
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
  Permalink  
Rate this: bad
good
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")....
  Permalink  
Rate this: bad
good
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#
  Permalink  
Comments
saqib.akhter at 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 at 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 at 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 at 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
0 OriginalGriff 381
1 Sergey Alexandrovich Kryukov 265
2 Praneet Nadkar 237
3 Marcin Kozub 225
4 /\jmot 189
0 OriginalGriff 8,284
1 Sergey Alexandrovich Kryukov 7,407
2 DamithSL 5,614
3 Maciej Los 4,989
4 Manas Bhardwaj 4,986


Advertise | Privacy | Mobile
Web04 | 2.8.1411023.1 | Last Updated 9 Jul 2012
Copyright © CodeProject, 1999-2014
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