MySQL dynamic date change

Question

I want to create table with name "dynamicdate" in which first column name is "datecolumn" which will contain 15 rows i.e. first row should represent today's date for example 08/08/2017 and following column shows subsequent date for example, 08/09/2017, 08/10/2017 till 15th row contains 08/23/2017.

Question 1: How do I fill 15 rows in a column with consecutive date simultaneously.

Now, for example date becomes 08/09/2017 (because august 8 is over) is the today'date and 08/24/2017 is date of 15th day.

Question 2: How do i update database everyday dynamically i.e. without querying database.

Storing it in db is not the best solution. If you want to display the dates dynamically to any UI, make the logic their and don't store it in db — Karan Desai, Aug 08 '17 at 04:27
Yes, this isn't something you'd typically handle in a database — Strawberry, Aug 08 '17 at 07:00

score 1 · Accepted Answer · answered Aug 08 '17 at 04:56

1

This you can do by creating a job. Every morning or night schedule this Job. A job will execute this procedure "p_Update_dynamicdate".

create proc dbo.p_Update_dynamicdate
as
Begin

    Declare @date as datetime, @count as int
    set @date =getdate()
    set @count =1

    truncate table dynamicdate --Delete old data

    while @count<=15 
    Begin
        insert into dynamicdate(Ddate)  
        select Dateadd(d,@count,getdate())
        set @count=@count+1
    End

End

answered Aug 08 '17 at 04:56

Tripurari Yadav

216
1
3
11

This procedure will also delete the data associated with dates – Aug 08 '17 at 05:15
Yes, but he has the only date in the table. if other columns are there then he needs to update instead of truncate. He can modify procedure as per his need. – Tripurari Yadav Aug 08 '17 at 05:24

score 0 · Answer 2 · answered Aug 08 '17 at 05:20

Solution to problem #1 : Already solved on StackOverflow :

MySQL - How can I add consecutive dates to many existing records?

Solution to problem #2 : Please look at Schedule and events on MySql documentation :

https://dev.mysql.com/doc/refman/5.7/en/event-scheduler.html

syntax: CREATE EVENT my_event

ON SCHEDULE

EVERY 1 DAY

STARTS '2014-04-30 00:20:00' ON COMPLETION PRESERVE ENABLE

DO # Your Update query

Please refer to below links for similar problem solution :

https://dba.stackexchange.com/questions/64208/scheduling-an-event-every-day-at-a-given-time

How to schedule a MySQL query?

MySQL dynamic date change

2 Answers2