CODEWITHSUNDEEP
mysqlsqldatabasedate
Pankaj Gadge
Pankaj Gadge

Reputation: 2814

MySql ADDDATE() Query: Adding two dates with 30 0r 60 days of period

I have the following table named 'product'

product_id (BIGINT) | product_name (VARCHAR) | expiration_date (DATE) | entered_date (DATETIME) |
 1234            | ABC                    |     NULL               |    2013-07-24 13:17:32  |
 6789            | ABC                    |     NULL               |    2013-07-19 12:04:12  |  
 1234            | ABC                    |     NULL               |    2013-07-05 13:34:16  |  
 2343            | ABC                    |     NULL               |    2013-06-04 17:16:51  |

I would like to update the expiration_date entries based on the eneterd_date column. If entered_date + 30 days > NOW(), then set the expiration_date = 30 days ahead else expiration_date = 60 days ahead

product_id (BIGINT) | product_name (VARCHAR) | expiration_date (DATE)  | entered_date (DATETIME) |
 1234               | ABC                    |  2013-08-24             |    2013-07-24 13:17:32  |
 6789               | ABC                    |  2013-08-19             |    2013-07-19 12:04:12  |  
 1234               | ABC                    |  2013-08-05             |    2013-06-05 13:34:16  |  
 2343               | ABC                    |  2013-08-24             |    2013-06-04 17:16:51  |

I am trying my hands on ADDDATE() and other date functions in MySql but dint't have any good luck so far.

Please let me know you could help me out in getting the following result.

enter code here

Upvotes: 0

Views: 1468

Answers (2)

CaveCoder
CaveCoder

Reputation: 791

can you please specify the problem with the ADDDATE() funciton?

I tryed something like this and it worked.

CREATE TABLE dates
    (
     id int auto_increment primary key, 
     date DATETIME DEFAULT NULL
    );

INSERT INTO dates
(date)
VALUES
(NOW());
UPDATE dates SET date = DATE_ADD(date,INTERVAL 10 DAY);

Maybe you have the where clause on your update statement wrong.

you can do it like this

 WHERE DATE_ADD(date ,INTERVAL 30 DAY) > NOW()

Upvotes: 1

Jarek.D
Jarek.D

Reputation: 1294

try this (not tested!)

 update product set expiration_date = (
      select if(dateadd(entered_date, interval 30 day) > now(),
          dateadd(entered_date,interval 30 day), 
          dateadd(entered_date,interval 60 day)))

Upvotes: 1

Related Questions