0

I am trying to insert a query : insert into Table(ID,Value,Date) Values('Test1', '33', '20210315 11:15:27.059') to MariaDB using ODBC Driver (Maria 3.1) but I have this error :

Exception message : ERROR [22007] [ma-3.1.11][10.2.15-MariaDB]Incorrect datetime value: '20210315 11:15:27.059' for column 'date' at row 1 I think that this format '20210315 11:15:27.059'is unsupportable by MariaDB.

Could you please help me to change the time format from the database.

thank you.

TruckerCat
  • 1,437
  • 2
  • 16
  • 39
happy
  • 1

1 Answers1

0

I forget the exact rules for what formats are acceptable as date/timestamp literals. However, I'm sure that the following format is acceptable:

2021-03-15 11:15:27.059

Your updated code:

INSERT INTO yourTable (ID, Value, Date)
VALUES ('Test1', '33', '2021-03-15 11:15:27.059');

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • thank you but I need this format 20210315 11:15:27.059 is there a way to change the date format from the Databases to support it? – happy Mar 15 '21 at 10:38
  • @happy Dates/timestamps have no internal "format," they are stored as binary. If you want to _view_ your timestamp this way, then use the `DATE_FORMAT` function with the mask you want. – Tim Biegeleisen Mar 15 '21 at 10:39
  • could you help me on how to use the DATE_FORMAT function? – happy Mar 15 '21 at 10:43
  • I executed it but I have this error : /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DATE_FORMAT(2021031015 11:46:23.320, '%Y-%m-%d %H:%i:%s.%f')' at line 1 */ DATE_FORMAT(DATE, '%Y-%m-%d %H:%i:%s.%f'); /* SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'DATE_FORMAT(DATE, '%Y-%m-%d %H:%i:%s.%f')' at line 1 */ – happy Mar 15 '21 at 10:49
  • @happy [Check the demo](https://dbfiddle.uk/?rdbms=mariadb_10.5&fiddle=60aa9626e163fe148edd9005c7fca6a1) – Tim Biegeleisen Mar 15 '21 at 10:51
  • Read the above comment which has a working demo. – Tim Biegeleisen Mar 15 '21 at 11:08
  • I used the same query on HeidiSql the query executed and the table is created but the format of the date displayed is 2021-03-15 11:15:27.059' I need that the format of the column date displayed in the table is as the following '20210315 11:15:27.059'. – happy Mar 15 '21 at 11:18
  • Your question has been answered. If you have other requirements, please open a new question. – Tim Biegeleisen Mar 15 '21 at 12:24