Hello all,

 

I have a database called "XYZ" and in there a table called "dates" in here lists all the dates of the year. e.g

 

 

How ever I need to now add all the dates of the year for 2016, is there a way I could do this using SQL Query? as it would takes me god knows how long to add every date of 2016 manually

 

Thanks in advance,

Matt.

 

 

I literally can't think of a single reason to do this. What are you trying to do that a timestamp can't achieve?

Unfortunately this was how the website was built, back in 2006.

I literally can't think of a single reason to do this. What are you trying to do that a timestamp can't achieve?

 

Does it really matter whether you can think of a single reason to do this, the OP has asked if there is a query that will achieve what he's after.

 

 

Running this script should do what you're after,

 

Make sure you backup your db first just in case

 

INSERT INTO XYZ.dates (`date`)
SELECT  DATE_ADD(date, INTERVAL 1 YEAR) FROM XYZ.dates;

 

This can be run straight from PHPMyAdmin or another Database management tool.

Edited February 24, 201610 yr by richardmountain

Thanks for help that worked a treat

 

While I understand its a messy way to do it, the website is very old and with lack of coding knowledge it will just have to do for the time being.

Strange tho as 29/01/2016 is present.

Does it really matter whether you can think of a single reason to do this, the OP has asked if there is a query that will achieve what he's after.

 

In this case, it's unavoidable because it's a legacy system.

 

It's worth asking anyway, on the chance that's there's a better long term solution. If you look at posts on here, you will see the same thing from other users. Working out why something is implemented in a particular way, helps to provide context, and will almost always lead to better answers. Ignoring the fact that this is bad practice doesn't help anyone, the OP may not have even been aware.

 

In this case, it's unavoidable because it's a legacy system.

 

It's worth asking anyway, on the chance that's there's a better long term solution. If you look at posts on here, you will see the same thing from other users. Working out why something is implemented in a particular way, helps to provide context, and will almost always lead to better answers. Ignoring the fact that this is bad practice doesn't help anyone, the OP may not have even been aware.

 

Fair enough.

You could do it using code behind using a loop to input each day of the year.

 

int cx;

for (cx=1;cx<=365;cx++)

add to sql database day cx;

Unless you need all future dates in the database for some reason you can do something like this.

 

CREATE EVENT `addDateDaily` ON SCHEDULE EVERY 1 DAY
STARTS '2015-12-30 00:00:00' ON COMPLETION PRESERVE ENABLE DO
  INSERT INTO `XYZ` (`date`) VALUES (NOW());

// If you always need the future year in the database
CREATE EVENT `addDateDaily` ON SCHEDULE EVERY 1 DAY
STARTS '2015-12-30 00:00:00' ON COMPLETION PRESERVE ENABLE DO
  INSERT INTO `XYZ` (`date`) VALUES (DATE_ADD(NOW(), INTERVAL 1 YEAR));

This would then create a record in the database every day so you wont have to do this ever again, you can also do this with a cron or windows scheduled task (depending on OS)

Edited March 7, 201610 yr by D4Y0