?
Solved

Date/Time format

Posted on 2003-03-02
8
Medium Priority
?
306 Views
Last Modified: 2010-04-01
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
Comment
Question by:Teppei78
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 2

Expert Comment

by:corduroy9
ID: 8051593

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
 

Author Comment

by:Teppei78
ID: 8051662
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
 
LVL 2

Expert Comment

by:corduroy9
ID: 8051703


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!

 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 8055136
// 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
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 8055145
>> 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
 

Author Comment

by:Teppei78
ID: 8055233
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
 
LVL 2

Accepted Solution

by:
corduroy9 earned 300 total points
ID: 8057210


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
 
LVL 30

Expert Comment

by:Axter
ID: 9372253
********************************************************************************************
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

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing generic code, using template meta-programming techniques, it is sometimes useful to know if a type is convertible to another type. A good example of when this might be is if you are writing diagnostic instrumentation for code to generat…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
The goal of the video will be to teach the user the difference and consequence of passing data by value vs passing data by reference in C++. An example of passing data by value as well as an example of passing data by reference will be be given. Bot…
The viewer will learn how to user default arguments when defining functions. This method of defining functions will be contrasted with the non-default-argument of defining functions.
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question