Incorrect datetime. What i am doing wrong?

Question

i am trying to insert random datetime into my table but when i call the procedure it show me error that is my datatime value incorrect and i do not know why. Please can someone help me and explain?

in datumPredvajanja i want to set only date in casPredvajanja i want to set only time how i do that? I am sure my random time generation in datum_predvajanja is correct.

My create statement:

CREATE TABLE IF NOT EXISTS `pb2_pb`.`Spored` (
  `ID_Spored` INT NOT NULL,
  `datumPredvajanja` DATETIME NOT NULL,
  `casPredvajanja` DATETIME NOT NULL,
  `aktualno` VARCHAR(45) NOT NULL
)
ENGINE = InnoDB;

My procedure:

DELIMITER //
CREATE PROCEDURE polni_spored (st_sporeda INT)
    BEGIN
        DECLARE datum_predvajanja DATETIME;
        DECLARE cas_predvajanja DATETIME;
        DECLARE aktualno VARCHAR(45);
        DECLARE stevec INT;
        DECLARE konecVal INT;

        SET stevec = 0;
        SET konecVal = st_sporeda;

        WHILE (stevec < konecVal) DO
            SET datum_predvajanja = (SELECT DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(NOW()) + FLOOR(0 + (RAND() * 63072000))), '%Y-%m-%d'));
            SET cas_predvajanja = NOW() - INTERVAL FLOOR(RAND() * 1000) DAY;
            SET aktualno = "DA";

            INSERT INTO `pb2_pb`.`Spored` (`datumPredvajanja`, `casPredvajanja`, `aktualno`) VALUES ('datum_predvajanja', 'cas_predvajanja', CONCAT(aktualno, (stevec + 1)));

            SET stevec = stevec + 1;
        END WHILE;
    END //

DELIMITER ;

Error Code: 1292. Incorrect datetime value: 'datum_predvajanja' for column 'datumPredvajanja' at row 1

Answer 1

You do not need to use single quotes around the user-defined string variables, while doing Insert.

It should be the following instead:

INSERT INTO `pb2_pb`.`Spored` (`datumPredvajanja`, 
                               `casPredvajanja`, 
                               `aktualno`) 
                       VALUES (datum_predvajanja, 
                               cas_predvajanja, 
                               CONCAT(aktualno, (stevec + 1))
                              );

Also, do not use double quotes for String literals. Although MySQL allows it, but it is not ANSI SQL standard compliant. It is always preferable to use single quotes around String literals.

Instead of, SET aktualno = "DA"; it should be:

SET aktualno = 'DA';

Also, it is a good practice to set default values for Declared variables. For eg:

DECLARE aktualno VARCHAR(45) DEFAULT '';
DECLARE stevec INT DEFAULT 0;
DECLARE konecVal INT DEFAULT 0;