Visual C++ 6 writing a date/time value into a database

Question

Visual C++ 6 writing a date/time value into a database

Evgeny Shupik 191

Hello everybody! :-) Need some advice about writing data in the database using MFC (Visual C++ 6). There is a table HISTORY containing fields: employer_code, device_code and Date. So first two fileds are 'long int' type and Date is a 'date/time' type. Trying to write a new record:

sSql.Format("INSERT INTO History (device_code,employer_code) VALUES ('%d','%d');", max_device_code,employer_code);
database.ExecuteSQL(sSql);

It's OK but I don't know how to write Date filed - what kind of VALUES should I provide? I mean:

sSql.Format("INSERT INTO History (device_code,employer_code, Date) VALUES ('%d','%d','%???');", max_device_code,employer_code,date);
database.ExecuteSQL(sSql);

Thank you for help.

David Lowndes 4,726 Reputation points

2022-05-22T18:51:08.69+00:00

Depending on the database you're using and the date column type in your database, the format you need will differ. See if the information here helps you.
Minxin Yu 13,501 Reputation points Microsoft External Staff

2022-06-09T08:39:39.867+00:00

May I know if your problem has been solved?

2 answers

Your answer

David Lowndes 4,726 Reputation points

2022-05-22T18:51:08.69+00:00

Depending on the database you're using and the date column type in your database, the format you need will differ. See if the information here helps you.
Minxin Yu 13,501 Reputation points Microsoft External Staff

2022-06-09T08:39:39.867+00:00

May I know if your problem has been solved?

Answer 1

Guido Franzke 2,191

Hello,
normally speaking, a database uses a date/time format in English notation, means: "yyyy-mm-dd hh:mm:ss".
You don't say what exact datatype you use for your date variable.
Read the documentation of your date datatype how to get the single values for year, month, day, hour, minute and second.
Then the source code would look like this (just replace the GetYear,GetMonth,... functions with the corresponding functions for your date variable):

sSql.Format("INSERT INTO History (device_code,employer_code, Date) VALUES ('%d','%d','%4i-%02i-%02i %02i:%02i:%02i'", max_device_code, employer, date.GetYear(), date.GetMonth(), date.GetDay(), date.GetHours(), date.GetMinutes(), date.GetSeconds());

Regards, Guido

Olaf Helper 47,436 Reputation points

2022-05-23T06:44:36.87+00:00

database uses a date/time format in English notation, means: "yyyy-mm-dd hh:mm:ss".

Guido, that's ODBC format (format code 112, see link) and should be prefered, because it's language independent.0
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

See also Write International Transact-SQL Statements
Evgeny Shupik 191 Reputation points

2022-05-23T12:43:43.5+00:00

This is Microsoft Access (*.mdb) database.
Olaf Helper 47,436 Reputation points

2022-05-23T12:58:46.657+00:00
This is Microsoft Access (*.mdb) database.

in MS Access you have to use US-EN format in hashtag, like #5/23/2022# for today.

SELECT * FROM Table1 WHERE Table1.DateTime = #5/22/2022#;
Evgeny Shupik 191 Reputation points

2022-05-29T15:39:01.03+00:00

Hello again! Unfortunately still no result... Please look:

CString sYear("2022");
CString sMonth("05");
CString sDay("06");
CString sHour("12");
CString sMinute("34");
CString sSecond("28");
Year=atoi(sYear);
Month=atoi(sMonth);
Day=atoi(sDay);
Hour=atoi(sHour);
Minute=atoi(sMinute);
Second=atoi(sSecond);

sSql.Format("INSERT INTO History (device_code,employer_code,Date) VALUES ('%d','%d','%4i-%02i-%02i %02i:%02i:%02i');", max_device_code,employer_code,Year,Month,Day,Hour,Minute,Second);
database.ExecuteSQL(sSql);

So I receive an error "syntax error in insert into statement" in this case.

Answer 2

Minxin Yu 13,501 Microsoft External Staff

Hi, @Evgeny Shupik

This is the test snippet for your reference:

db.ExecuteSQL(  
		_T("INSERT INTO Table1  ")  
		_T("VALUES  (5,'5/30/2022 4:02:05 AM') ")//ID Date

Best regards,

Minxin Yu

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Evgeny Shupik 191 Reputation points

2022-06-02T13:03:04.597+00:00

Please look at this:
sSql.Format("INSERT INTO History (device_code,employer_code,Date) VALUES ('1','95','12.12.2021');"); //added manually without any variables

So here is value of sSql:

And, anyway, there is a same error about invalid statement.

P.S. Here is screenshot from my Access DB, table "History" - what is wrong with it?
Minxin Yu 13,501 Reputation points Microsoft External Staff

2022-06-06T06:20:20.703+00:00

According to the document: Insert, update, and delete records from a table using Access SQL :

INSERT INTO tblCustomers (CustomerID, [Last Name], [First Name])
VALUES (1, 'Kelly', 'Jill')

Try to add [ ].

Share via

Visual C++ 6 writing a date/time value into a database

2 answers

Your answer