• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

Date/Time format

Hi, I am writing a program using Visual C++.
I need to make a connection to the database. I did it using the below code...

CString Date1="01/02/2003";
pCommand->ActiveConnection = m_pConn;
CString sSQL; sSQL.Format(
"SELECT * FROM ConstructionSequence " 
"WHERE StartDate>'%s' ",
(LPCSTR) Date1

pCommand->CommandText = (_bstr_t) sSQL;
.....

I have this problem, I am currectly using StartDate (a field in the database) as a text format. This code works only when StartDate is in Text format but not when it is in Date/Time format. If I were to change StartDate from text format to date/time format, how should I change the code

Please advice~ Thanks

0
Teppei78
Asked:
Teppei78
  • 3
  • 2
  • 2
  • +1
1 Solution
 
corduroy9Commented:

Have you tried using CTime objects?  

What DB are you using?  If you are using MS SQL Server, try running this command in Query Analyzer:

SELECT ISDATE( "01/02/2003" )

It will return 1 (true) or 0 (false) if the date format is valid.

I assume every database has a similar scratchpad for testing SQL statements.  Perhaps your database does not "like" the way you have the date formatted.  Try other varieties, such as:

SELECT ISDATE( "01-02-2003" )
SELECT ISDATE( "2003-01-02" )

After you figure out what format your database likes, you can use CTime and CTime.Format to format your date into a CTring appropriately.

Good luck...


0
 
Teppei78Author Commented:
Hi, I am using MS Access.

Previously I have written something like this and it works
....

strQuery = "Select * From BridgeConstructSeq Where EndDate <= #";
strQuery += Dlg.m_FromDate.Format("%m/%d/%y");
strQuery += "#";
pCommand->ActiveConnection = m_pConn;
pCommand->CommandText = (_bstr_t)strQuery;

So I suppose the format should be in this format ("%m/%d/%y")

But in next my code...even though my Date1 is in just the same format, it just won't work.

CString Date1="01/02/03";
pCommand->ActiveConnection = m_pConn;
CString sSQL; sSQL.Format(
"SELECT * FROM ConstructionSequence " 
"WHERE StartDate>'%s' ",
(LPCSTR) Date1

pCommand->CommandText = (_bstr_t) sSQL;

Can somebody give some comments...
0
 
corduroy9Commented:


I've never used MS Access before, but I just set up a test table with a Date/Time column and entered a row with 01/02/03 as the date/time.

I then inserted a query (with a query wizard) just to get the date, and I set the criteria to get 01/02/03.  I ran the query and my row came back.  Then under View, I selected SQL View.

It shows the query that works.

It appears you need the # signs around the date string, not the ' signs.

Try that.

(That kinda looks like the only difference from your examples above also.)

Let me know...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kyle AbrahamsSenior .Net DeveloperCommented:
// sucks in the current date from the system and converts it to mm-dd-yyyy format.


char date_string[20] = {0}; //declaration of date_string, a global variable.  will leave it in text format.
void Get_Date()
{
   struct tm *timer;
   time_t aclock;
   time( &aclock );                 /* Get time in seconds */
   timer = localtime( &aclock );  /* Convert time to struct */

   strftime(date_string, 20, "%m-%d-%Y", timer);
}
0
 
Kyle AbrahamsSenior .Net DeveloperCommented:
>> This code works only when StartDate is in Text format but not when it is in Date/Time format. If I were to change StartDate from text format to date/time format, how should I change the code

why change it to date/time format if it works only when in text format?
0
 
Teppei78Author Commented:
Quite true, it works in text.. I need not change to date/time, but just for the sake of knowing, I want to find out how it works.
Anyway corduroy9
you mentioned
"It appears you need the # signs around the date string, not the ' signs."

I don't quite get what u meant by "need the # signs around the date string, not the ' signs."

May I take a look at your code??
Thanks
 
0
 
corduroy9Commented:


Teppei78,

I just ran a query (from home, now I'm at work) and it was something like this:

Select TestDate
From TestTable
Where TestDate = #01/02/03#

Have you tried formatting your SQL statement like that:

strQuery.Format( "Select TestDate From TestTable Where TestDate = #%s#", Date1 );



0
 
AxterCommented:
********************************************************************************************
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Accept [corduroy9 ]'s comment as answer

Please leave any comments here within the next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

Axter
EE Cleanup Volunteer
********************************************************************************************
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now