[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Date Format / SQL Query..

Posted on 1999-11-05
7
Medium Priority
?
337 Views
Last Modified: 2010-04-01
I have a nice problem with the date format in my Access DB and a SQL query.

First of all some code:

//(table1 = CDaoRecordset*)
CString sel_time;
sel_time = table1->date_field.Format("%Y-%m-%d%H:%M:%S");

CString SQL_select = "select * from [table_name] where (\"Last Updated\" > DateValue(#" ;
SQL_select += sel_time;
SQL_select += "#) )";

Hope there are no typos....

Anyway. The problem I have is that the result of my query is the  whole table "table_name" and not just the ones which have the correct date. As some of you might have guessed already the bug lies in the format of the date. My Access DB Date Format is "31.01.99" (good ol' european style).

Who paid attention and isn't confused (I am) and can help me before I go home and drink.

Thanks (cry for help......)
0
Comment
Question by:Stapman
  • 4
  • 2
7 Comments
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2186802
>> the bug lies in the format of the date. My Access DB Date Format is "31.01.99"
Doesn't matter to much, dates are internally stored 'correct'. That is only the display/entry format

What is the contents of sel_time?
Try a query like
  CString sql = "SELECT DateValue(#" ;
  sql += sel_time;
  sql += "#);
to review what is the result of the datevalue function.
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2186812
Secondly, what are you doing? You get a field from one table, and use its value in the where clause of another query? Sounds like a JOIN to me.
0
 

Author Comment

by:Stapman
ID: 2186949
Content of sel_time is something like "1999-10-15 10:35:15". What I'm doing is following: I'm trying to get several datasets which have a certain date and insert these into a recordset. I open a recordset with the SQL query (SQL_select).
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:Stapman
ID: 2186965
By the way: I'm using VC++ 6.0 and in my "Variables" window the table with table_name (the one which is used for the query) has some interesting values. When I look at the table attribute "Last Updated"  has the value "36355.000000000". Is that the internal date format you mentioned??? All other values are "correct" i.e. look the same in Access.
0
 

Author Comment

by:Stapman
ID: 2186973
By the way: I'm using VC++ 6.0 and in my "Variables" window the table with table_name (the one which is used for the query) has some interesting values. When I look at the table attribute "Last Updated"  has the value "36355.000000000". Is that the internal date format you mentioned??? All other values are "correct" i.e. look the same in Access.
0
 

Accepted Solution

by:
Olethros earned 200 total points
ID: 2202472
I had a similar problem using C++ Builder and Access97.
To solve the problem, I tested my queries directly in Access
and found a very interesting SQL date format. Here an example:

SELECT * FROM Blabla
WHERE date >= #12-24-98 09:00:00#

The displayed (country dependent) date format differs from the internal Access date format (float), and the SQL date format is
#mm-dd-yyyy hh:mm:ss#
Confusing, innit?

So you should build your date string with

sel_time = table1->date_field.Format("#%m-%d-%Y %H:%M:%S#");

Since i don't know much about Format(), I hope the hash marks are ok here...

But your SQL string looked a little strange (DateValue?!)
Perhaps you should try the following code:

CString SQL_select = "select * from [table_name] ";
SQL_select += "where [Last Updated] > ";
SQL_select += sel_time;

That should do the trick, but mind the spaces at the end of the strings! And don't try to put the date string between quotes, the
hash marks are all you need to limit the date string.
0
 

Author Comment

by:Stapman
ID: 2234341
thanks
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

Question has a verified solution.

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

Errors will happen. It is a fact of life for the programmer. How and when errors are detected have a great impact on quality and cost of a product. It is better to detect errors at compile time, when possible and practical. Errors that make their wa…
This article will show you some of the more useful Standard Template Library (STL) algorithms through the use of working examples.  You will learn about how these algorithms fit into the STL architecture, how they work with STL containers, and why t…
The goal of the tutorial is to teach the user how to use functions in C++. The video will cover how to define functions, how to call functions and how to create functions prototypes. Microsoft Visual C++ 2010 Express will be used as a text editor an…
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…
Suggested Courses

612 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