MySQL: inserting datetime.date(2023,6,22) into a table

Question

I have an sql file containing, say,

INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));

How do I create a table that will accept this? More explicitly, in

CREATE TABLE mytable(ID INT NOT NULL, Date ???)) ENGINE=INNODB;

what should I put in place of '????'?

When I substitute varchar(12) for ????, I get the error message:

mysql.connector.errors.ProgrammingError: 1630 (42000): FUNCTION datetime.date does not exist. Check the 'Function Name Parsing and Resolution' section in the Reference Manual.

Checking doesn't reveal any insights, at least for me. What should I be doing?

Your question seem to be: "How to create a date when year, month and day are given". That question has already answer here: [Create date from day, month, year fields in MySQL](https://stackoverflow.com/questions/3960049/create-date-from-day-month-year-fields-in-mysql) — Luuk, Jul 30 '23 at 09:05

score 1 · Answer 1 · answered Jul 30 '23 at 13:08

datetime.date(2023, 2, 22) fails because there is no function date in the datetime schema/database. One "dirty workaround", to avoid doing a mass string replace on your SQL file, is to create a date function in the datetime schema. MySQL will raise a 1585 warning, due to their being a native function with the same name, but it will allow you to create it. This is a lazy version as I have not done any validation on the inputs, but hopefully you get the idea:

CREATE SCHEMA `datetime`;
USE `datetime`;

CREATE FUNCTION `datetime`.`date` (y SMALLINT UNSIGNED, m TINYINT UNSIGNED, d TINYINT UNSIGNED)
  RETURNS DATE DETERMINISTIC
  RETURN DATE(CONCAT_WS('-', y, m, d));

SELECT datetime.date(2023, 2, 22);

A better option would be to modify your SQL file with sed:

sed -i -E "s/datetime.date\(([0-9]{4}), ([0-9]{1,2}), ([0-9]{1,2})\)/'\1-\2-\3'/" your_sql_file.sql

which will rewrite your_sql_file.sql from:

INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));
INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 3, 23));

to:

INSERT INTO mytable (ID, Date) VALUES (1, '2023-2-22');
INSERT INTO mytable (ID, Date) VALUES (1, '2023-3-23');

Many thanks for this. I shall try it both ways. I am not familiar with sed, though it looks to be powerful tool. — Cecil Scott, Jul 31 '23 at 09:31

szy · Answer 2 · 2023-07-30T08:55:06.870

0

You can directly pass a date in string format (ex. '2023-02-22'), notice the quotes since it should be a string. Also, the data type of the column should simply be a DATE.

Create table: CREATE TABLE mytable(ID INT NOT NULL, Date DATE) ENGINE=INNODB;

Insert values: INSERT INTO mytable (ID, Date) VALUES (1, '2023-02-22');

edited Jul 30 '23 at 08:55

answered Jul 30 '23 at 08:54

szy

1
1

The date value is not a string in my case but 'datetime.date(2023, 2, 22)' without the quotes. There are thousands of these in the sql file. I could, of course, modify the sql file and replace all datetime.date's with an equivalent string and my problem would be solved in a roundabout way. However, the file was given to me with datetime.date's, so I guess there must be a direct way, perhaps using functions? Any thoughts? – Cecil Scott Jul 30 '23 at 09:12
Does having the 'DATE' data type in place of '???' give the same error? – szy Jul 30 '23 at 09:19

score 0 · Answer 3 · answered Jul 30 '23 at 09:05

0

The error message says that MySQL is interpreting datetime.date(2023, 2, 22) as a function call, which doesn't exist in MySQL, hence the error. This is because you used datetime.date(2023, 2, 22) inside your INSERT INTO statement, and it seems that the MySQL connector is treating it as a function rather than a date value.

CREATE TABLE mytable (
  ID INT NOT NULL,
  Date DATE,
  PRIMARY KEY (ID)
) ENGINE=INNODB;

So ideally you should convert the date objects to the desired MySQL date format and then use that in your INSERT queries.

answered Jul 30 '23 at 09:05

Beulah Evanjalin

20
2

Thank you, Beulah. I can see what you are getting at. Unfortunately, the INSERT INTO....VALUES(1, datetime.date(2023, 2, 22)); isn't my construct. It is just an example of what is in a very large sql file, each row of which contains an INSERT clause with a datetime.date VALUE. Of course, I can search for the datetime.date in each line of the sql file and convert it to a string and then execute the INSERT commands on each line. Is there a way for MySQL to recognize the datetime.date user specified function? – Cecil Scott Jul 30 '23 at 10:07
Check if this works https://stackoverflow.com/a/76798441/22308196 – Beulah Evanjalin Jul 30 '23 at 15:28

score -1 · Answer 4 · answered Jul 30 '23 at 15:27

Create the schema named datetime and switch to the datetime schema:

CREATE SCHEMA datetime;
USE datetime;

Create the function date in the datetime schema:

DELIMITER //

CREATE FUNCTION date(year_val INT, month_val INT, day_val INT)
RETURNS VARCHAR(10)
DETERMINISTIC
BEGIN
    DECLARE date_str VARCHAR(10);

    SET date_str = CONCAT(
        year_val,
        '-',
        LPAD(month_val, 2, '0'),
        '-',
        LPAD(day_val, 2, '0')
    );

    RETURN date_str;
END //

DELIMITER ;

So the script to create the mytable table in the datetime schema would be:

CREATE TABLE mytable (
    ID INT NOT NULL,
    `date` DATE,
    PRIMARY KEY (ID)
) ENGINE=INNODB;

and the insert query would be:

INSERT INTO mytable (ID, Date) VALUES (1, datetime.date(2023, 2, 22));

When I would create this function, I would have it returned a `DATE`, and not a `VARCHAR(10)`, because it's illogical to return a varchar when you need a date. — Luuk, Jul 30 '23 at 15:58
Interesting. It worked the first time after creating the date function. It failed on subsequent times: 'Selected name conflicts with existing function 'date'. — Cecil Scott, Aug 20 '23 at 18:47

MySQL: inserting datetime.date(2023,6,22) into a table

4 Answers4