Adding a NULL value for a datetime entry in an SQL table through Bash

Question

I am attempting to populate an SQL table through reading text files via bash to hand off to the table. I have it all working except when trying to add a null value to a datetime field.

mysql --host 10.100.10.19 --port 3306 -utest -ptest << EOF
INSERT INTO imaging.imagestatus (StatusDate, FileName, FolderName, Status, User, CaptureDate, Kind, Preview) VALUES ('2013-05-01T15:25:55', 'fileName1', 'folderName1', 'Status1', 'User1', 'NULL', 'kind1', 'preview1');
EOF

I keep getting:

ERROR 1292 (22007) at line 1: Incorrect datetime value: 'NULL' for column 'CaptureDate' at row 1

for the value labeled 'NULL' which is a data type datetime in the table. I have tried the combinations '\N', '\N', 'NULL', 'null' and ' '

I started to use a temporary date: 00-00-00 00:00:00 but I want it to match other null entry formats or just a blank space in the table. Thanks for the help!

ANSWER-- As was implemented

The value in the text file name "NULL" that is pulled in can be swapped with a variable like a=NULL to get a correct NULL value for a datetime input.

Dan J · Accepted Answer · 2013-05-03 19:30:08Z

8

If you're using quotes, then you're saying that you want the date to be the string 'NULL'. Did you try just using NULL?

INSERT INTO imaging.imagestatus (StatusDate, FileName, FolderName, Status, User, CaptureDate, Kind, Preview) 
VALUES ('2013-05-01T15:25:55', 'fileName1', 'folderName1', 'Status1', 'User1', NULL, 'kind1', 'preview1');

edited May 3, 2013 at 19:30

Dan J

16.8k7 gold badges53 silver badges82 bronze badges

answered May 3, 2013 at 19:14

Colin

1688 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Sleepykrooks Over a year ago

Ah, I don't know how I didn't try that. How embarrassing. I just need to have an if statement check if the text value is the string "NULL" and if so, insert a variable a=NULL into. Thanks for the quick response and your time!

Collectives™ on Stack Overflow

Adding a NULL value for a datetime entry in an SQL table through Bash

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related