Failing when inserting generated column of date or time type from a datetime/timstamp source

100
January 01, 2020, at 11:40 AM

A generated column is a column containing the result of an expression or function based on another field. I want a simple date and time field derived from a datetime field, but to insert records I must adopt a workaround.

This is the environment:

MySql 5.7.28

sql_mode=... , NO_ZERO_IN_DATE, NO_ZERO_DATE, ...

session.explicit_defaults_for_timestamp=ON

Table creation statement:

CREATE TABLE TEST.`Test_1` ( `Dossier_Code` varchar(12) COLLATE utf8_unicode_ci DEFAULT NULL, `REQ_DateTime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, `REQ_Date` date GENERATED ALWAYS AS (DATE(`REQ_DateTime`)) STORED, `REQ_Time` time GENERATED ALWAYS AS (TIME(`REQ_DateTime`)) STORED) ENGINE=MyISAM;

Insert statement failed:

INSERT INTO Test_1(Dossier_Code) VALUES ('ABC');

Error Code: 1292 Incorrect date value: '0000-00-00' for column 'REQ_Date' at row 1

Insert statement working with warning:

INSERT IGNORE INTO Test_1(Dossier_Code) VALUES ('ABC');

Warning Code : 1264 Out of range value for column 'REQ_Date' at row 1

I'm usually strict in writing queries and codes and I don't like the word "IGNORE" in any case. I also understand that MySql inserts values into the generated columns after writing the record emulating a post-insert trigger.

Anyone knows a better method to insert records without "IGNORE". Thanks MimCris

READ ALSO
What is the best way to load the bootstrap card with contents dynamically?

What is the best way to load the bootstrap card with contents dynamically?

I am a beginner in web developmentI need to create a page with bootstrap card dynamically by fetching values from database

97
Why a named function declaration isn't hoisted if you put it inside the return statement?

Why a named function declaration isn't hoisted if you put it inside the return statement?

is putting the function declaration beside the return statement causing the function not to hoist? or putting it inside the return statement turns it into a function expression that's why it isn't hoisted?

68
how to set the parameter NODE_EXTRA_CA_CERTS in the project instead of the scripts

how to set the parameter NODE_EXTRA_CA_CERTS in the project instead of the scripts

I have an api testing project build with nodejs and Jest which send http requests to multiple domain using the 'request-promise' npm package

146
Angular 4 Universal SSR- ReferenceError: document is not defined

Angular 4 Universal SSR- ReferenceError: document is not defined

I am trying to accomplish Server side rendering for Angular 4 but getting error "ReferenceError: document is not defined" -some people suggested to add dominojs but still getting error after adding domino js in server

154