-1

I'm trying to insert an incomplete date into a mysql table with a column of type date called video_date with the following query

UPDATE videos SET video_date = '1961-00-00' WHERE video_id = 6

However the date just gets set to 0000-00-00. When I try typing the above query into the SQL query window in PHPmyadmin it gives me the following error "Warning: #1265 Data truncated for column 'video_date' at row 1" and the date in the column remains as 0000-00-00.

I cant figure out what the problem is because according to these stackoverflow threads it should be possible to insert incomplete dates, such as '1961-00-00' or '1961-05-00':

Store incomplete date in MySQL date field

How to insert just year and month into date field?

Any ideas? Thanks!

EDIT added quotes around the date but the same problem persists.

coding_pianist
  • 71
  • 2
  • 10
  • 1
    it is an incorrect date, because it doesn't exist, make a varchar out of it and live with converting it, or simple update a normal date – nbk Aug 08 '20 at 00:10
  • It's not even a date expression but an arithmetic expression resulting in `1961`. – sticky bit Aug 08 '20 at 00:33
  • Does this answer your question? [When to use single quotes, double quotes, and backticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks-in-mysql) – sticky bit Aug 08 '20 at 00:36
  • @stickybit I tried `UPDATE videos SET video_date = '1961-00-00' WHERE video_id = 6` and also tried using other delimiters such as @ as per the linked Mysql documentation in the top answer, but I still get the same problem when running the above query in PHP myadmin. @nbk did you look at the threads I linked which explicitly state that Mysql should permit dates of formats such as '2014-00-00'? – coding_pianist Aug 08 '20 at 00:56
  • *Structured* Query Language. This implies that data storage is strict. The acceptance of `0000-00-00` was a mistake that is being slowly deprecated. If you want incomplete month, incomplete day of month, I'd suggest putting those as BOOLEAN fields and have the date have` 01` for those. Handle the representation to the user when you retrieve those fields. – danblack Aug 08 '20 at 01:06

1 Answers1

1

This might be due to your mysql_mode set with the STRICT_TRANS_TABLES which includes the mode NO_ZERO_IN_DATE.

Strict SQL Mode

Neand
  • 11
  • 2