Unable to insert date to SQL table

Question

I'm trying to insert a new record into a Prestashop SQL database and all works well till I need to introduce date records.

If I try to execute this line, I get an error saying "You have an error in your SQL syntax"

INSERT INTO ps_specific_price 
    (id_product, [reduction], [reduction_type]
                    , [from], [to]) 
 VALUES (100145, 0.15, "percentage"
                    , "2016-10-10 00:00:00", "2017-10-10 00:00:00")

If I use the same but without the date, it's working:

INSERT INTO ps_specific_price 
      (id_product, [reduction], [reduction_type]) 
  VALUES (100145, 0.15, "percentage")

I'm using this code from VBA with an ODBC connector and MySQL ODBC 5.3 ANSI Driver.

What is wrong with my code? I've tried different time formats, with and without hour stamp... not working...

Thanks!

Here is the schema:

'id_specific_price' int(10) unsigned NOT NULL AUTO_INCREMENT,
'id_specific_price_rule' int(11) unsigned NOT NULL,
'id_cart' int(11) unsigned NOT NULL,
'id_product' int(10) unsigned NOT NULL,
'id_shop' int(11) unsigned NOT NULL DEFAULT '1',
'id_shop_group' int(11) unsigned NOT NULL,
'id_currency' int(10) unsigned NOT NULL,
'id_country' int(10) unsigned NOT NULL,
'id_group' int(10) unsigned NOT NULL,
'id_customer' int(10) unsigned NOT NULL,
'id_product_attribute' int(10) unsigned NOT NULL,
'price' decimal(20,6) NOT NULL,
'from_quantity' mediumint(8) unsigned NOT NULL,
'reduction' decimal(20,6) NOT NULL,
'reduction_tax' tinyint(1) NOT NULL DEFAULT '1',
'reduction_type' enum('amount','percentage') NOT NULL,
'from' datetime NOT NULL,
'to' datetime NOT NULL,
PRIMARY KEY ('id_specific_price'),
UNIQUE KEY 'id_product_2' ('id_cart','id_product','id_shop','id_shop_group','id_currency','id_country','id_group','id_customer','id_product_attribute','from_quantity','id_specific_price_rule','from','to'),
KEY 'id_product' ('id_product','id_shop','id_currency','id_country','id_group','id_customer','from_quantity','from','to'),
KEY 'from_quantity' ('from_quantity'),
KEY 'id_specific_price_rule' ('id_specific_price_rule'),
KEY 'id_cart' ('id_cart')

Can you show your table schema for `ps_specific_price`? And if this is MySQL, you should tag as such. — SS_DBA, Nov 22 '16 at 20:02
Does this work `INSERT INTO ps_specific_price ([from], [to]) VALUES ('2016-01-01', '2016-04-04')`? — surfmuggle, Nov 22 '16 at 20:06

score 0 · Answer 1 · answered Nov 22 '16 at 20:05

Try this

 INSERT INTO ps_specific_price 
   (id_product, [reduction], [reduction_type]
                , [from], [to]) 
VALUES (100145, 0.15, "percentage"
                  , cast ( "2016-10-10 00:00:00" as datetime), cast ("2017-10-10 00:00:00" as datetime )

score 0 · Accepted Answer · edited May 23 '17 at 11:59

0

Got it! I just saw this message in phpmyadmin: "The column name 'to' is a MySQL reserved keyword."

So I checked it and replaced "from" with "backtick from backtick " as suggested here Syntax error due to using a reserved word as a table or column name in MySQL.

Thanks!

edited May 23 '17 at 11:59

Community

1
1

answered Nov 23 '16 at 06:32

user3254924

83
2
14

Unable to insert date to SQL table

2 Answers2