mySql Date/DateTime default value

Question

I have a Mysql 5.5 and a table with a column as follow:

`VERSION_TS` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP

but i'm having difficulties lately i'm getting this exception:

Incorrect datetime value: '1998-03-20' for column 'VERSION_TS' at row 1

when trying to enter values to the table, values of type Date, using mySql MakeDate(). Now, i can't change the function that return Date, but i can change the column to Date, but then i'll lose the default value. i've tried a couple of things, and then checked the web, and from what i understand in Mysql 5.5 there is no way to do it, but i could be wrong, so i came here to ask:

Is there a way that i can change the column to a Date and still have a default value?
Also, is there a better way to do approach the problem?

can you tell in which language the code is written which returns Date. — Suchit kumar
– Suchit kumar, Commented Dec 3, 2014 at 12:17

Gordon Linoff · Accepted Answer · 2014-12-03 12:19:44Z

1

I think the documentation is quite clear on this point:

TIMESTAMP and DATETIME columns can be automatically initialized and updated to the current date and time (that is, the current timestamp).

So, this does not apply to DATE. However, you could create a view that does what you want:

create view v_table as
    select t.*, date(version_ts) as version_date
    from table t;

answered Dec 3, 2014 at 12:19

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

Comments

Suchit kumar · Accepted Answer · 2014-12-03 12:31:13Z

1

if the function which returns date is written in php then after receiving the date value you can do this:

  $date = new DateTime('2006-12-12');
            echo date_format($date,'Y-m-d H:i:s');// this you can store in mysql table. 

  if not same kind of approach you can apply in the respective language to do the job.

OR work with view as Gordon Linoff mentioned.

answered Dec 3, 2014 at 12:31

Suchit kumar

11.9k3 gold badges26 silver badges46 bronze badges

Collectives™ on Stack Overflow

mySql Date/DateTime default value

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related