CODEWITHSUNDEEP
mysqlsql
urfusion
urfusion

Reputation: 5501

can't alter the default value from null to 0000-00-00 for date column

I have a table in which previous developer make a date type column with default value null. Now I want to change the default value to 0000-00-00. but I am getting below error.

1067 - Invalid default value

All records are in format of 0000-00-00.

is there any way to do this.

enter image description here

Client updated the mysql version So facing the below issue.

Able to alter table with using

-- phpMyAdmin SQL Dump
-- version 4.5.5
-- http://www.phpmyadmin.net
--
-- Host: localhost:3306
-- Generation Time: Mar 17, 2016 at 03:59 PM
-- Server version: 5.7.11
-- PHP Version: 5.6.14

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

Without header update, insert and alter query is not working

Upvotes: 0

Views: 582

Answers (1)

Syeknom
Syeknom

Reputation: 86

Have you tried using quotes around the date? '0000-00-00'

This should be the correct zero value for a DATE type (mySQL reference manual 11.3 Date and Time Types)

If it still doesn't work, perhaps you can check the value of NO_ZERO_DATE.

Upvotes: 2

Related Questions