Date + 3 yrs

zzz

I am writing a query to input data into a table. I need to add two dates: today and the one in three years. Can i do something like:

$db->query("INSERT INTO t1 ( begin_date, expire_date) 
VALUES (CURDATE(), ADDDATE(CURDATE(),INTERVAL 3 YEAR))");

edwardsbc

For MySQL, this appears to be approximately correct (I didn't try it).

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

zzz

I think I need DATE_ADD instead of ADDDATE.

zzz

I guess I was wrong. I got both entries as: 0000-00-00

crazychris

the the fields date, datetime or timestamp?
When I run:
select date_add(curdate(), interval 3 year)
I get:
2009-02-14 00:00:00

zzz

Yes, unfortunatelly that's what I get...

In fact after more research I decided that the correct syntaxt should be:

VALUES (NOW(), 'ADDDATE(NOW(), INTERVAL 3 YEAR)' )

Without ' ' I was getting an error. ADDDATE seem to be the right function to use. I'm getting a bit impatient since I've debugged everything but this...

Both fields are date.

:mad:

cbj4074

Do your date functions need to be SQL functions?

Without looking at a SQL reference manual, you could just use PHP to do your date calculations. Also, is there any good reason for using MySQL's native date format over UNIX timestamps? I've found UNIX timestamps far easier to deal with.

$in_3_years = mktime(date("G"),date("i"),date("s"),date("n"),date("j"),(date("Y") + 3));

$db->query("INSERT INTO t1 ( begin_date, expire_date)
VALUES (UNIX_TIMESTAMP(NOW()), " . $in_3_years . ")");

The above code, of course, assumes that the MySQL server and the PHP server are in the same time zone and are set to the same time (or are literally the same server). For the sake of consistency, you may want to use:

$in_3_years = mktime(date("G"),date("i"),date("s"),date("n"),date("j"),(date("Y") + 3));

$db->query("INSERT INTO t1 ( begin_date, expire_date)
VALUES (" . time() . ", " . $in_3_years . ")");

Even if this is just a temporary solution, if will work, and if you'd still prefer to use the native MySQL date formats, you can use date(), strftime(), or some other preferred function to format the dates.

Just some ideas.

zzz

It seems that it's easier to have MySQL function in this particular case, isn't it?

cbj4074

Probably, but you seem to be in a hurry 🙂

zzz

I am... but I'd like to get it right. Both of the servers are on the same machine, so they probably have the same settings. I am not concerned about the time, I only need dates.

crazychris

format the date to suit

select date_format(date_add(curdate(), interval 3 year),"%Y%m%d")

zzz

You mean I need to insert it, something like that?

$db->query("INSERT INTO t1 ( begin_date, expire_date) 
VALUES (NOW(), 'date_format(ADDDATE(NOW(), INTERVAL 3 YEAR)'), "%Y%m%d")'");

I do not have problems inserting now() into date field...

crazychris

INSERT INTO t1 ( begin_date, expire_date)
VALUES (NOW(), date_format(ADDDATE(NOW(), INTERVAL 3 YEAR), "%Y%m%d"))

zzz

OK, I am messing something somewhere.

begin_date -- NOW() works fine

expire_date -- is still 0000-00-00

crazychris

echo the query to screen, see if the layout/values are bad somehow...

zzz

Got it!

It was an extra char: "%Y%m%d"))

edwardsbc

For everyone's benefit, please post what finally work (the whole insert), and then mark the thread "Resolved" with the thread tools. Thanks!