CODEWITHSUNDEEP
mysqldatabasedate
user1852728
user1852728

Reputation: 161

How to change date in database

In my Mysql database, i have a set of date. How can I increase for each and every date in my database by 3 years ?

For example the current date is : 2 January 2001

I want the date to be increased by three years: 2 January 2004

Upvotes: 2

Views: 2840

Answers (3)

Dhinakar
Dhinakar

Reputation: 4151

Below query is to update the date column and also changed to date format. 

update date1 set dates = 
DATE_ADD( (str_to_date(concat( 
SUBSTRING_INDEX( dates,  ' ', 1 ),'-',
Substring(SUBSTRING_INDEX( SUBSTRING_INDEX( dates,  ' ', 2 ) ,  ' ', -1 ),1,3) ,
'-', 
SUBSTRING_INDEX( dates,  ' ', -1 )),'%d-%M-%Y')), INTERVAL 3 YEAR )

OR

The second query is simply update the year by 3.

update date1 set dates = 
concat( SUBSTRING_INDEX( dates,  ' ', 2 )  ,  ' ', 
(SUBSTRING_INDEX( dates,  ' ', -1 ) +3) )

SQL Fiddle Demo

Upvotes: 0

swapnesh
swapnesh

Reputation: 26732

Try this -

UPDATE TABLE set fieldname =  DATE_ADD( fieldname, INTERVAL 3 YEAR )

For more information and play part with dates you can check this link :-

function_date-add

Working Fiddle -- http://sqlfiddle.com/#!2/9c669/1

EDIT

This solution updates date type is VARCHAR and structure of date like - 2 January 2001

It will update date to 2 January 2004 by the interval of 3

Although the best way to handle date is use date DATATYPEs(ex timestamp, datetime etc) instead of saving it in VARCHARs

Tested code --

UPDATE date 
SET `varchardate`= DATE_FORMAT(DATE_ADD(  str_to_date(`varchardate`, '%d %M %Y'), INTERVAL 3 YEAR ) , '%d %M %Y')

Upvotes: 2

4d4c
4d4c

Reputation: 8169

Check date_add function, it should do the thing -http://www.w3schools.com/sql/func_date_add.asp

Upvotes: 0

Related Questions