How do I set MySQL column to current date on insert only?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
3dron
Forum Commoner
Posts: 40
Joined: Sat Feb 01, 2003 10:25 pm

How do I set MySQL column to current date on insert only?

Post by 3dron »

Where do I apply this:

TIMESTAMP columns other than the first may also be set to the current date and time. Just set the column to NULL or to NOW().

If I set the column to NULL or put NOW() in the default value then date comes up 0000-00-00 upon record insert.

If I put a hidden field and submit 0000-00-00 I get same result.

So how do I just get date once upon record creation?

Thanks

RR
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

try this

Post by phpScott »

I just spent a bunch of time playing with datefields and the like and the only way i could consistenly get it to work right was by doing this.

INSERT INTO someTable (name, address, date) VALUES($name, $address, date_format(NOW(), "%Y%m%d"));

this will explicitly set the NOW value that is being generated and make sure that it will be set into the db as 2003/02/06 or something like that.

phpScott
3dron
Forum Commoner
Posts: 40
Joined: Sat Feb 01, 2003 10:25 pm

Post by 3dron »

Ok, I guess I'm stupid.

I can't get it to work. Can it be done with a date field?
I am using your exact form of

date_format(NOW(), "%Y%m%d")

right in the date VALUE (between the commas with a space)

Ah, help!!!
User avatar
phpScott
DevNet Resident
Posts: 1206
Joined: Wed Oct 09, 2002 6:51 pm
Location: Keele, U.K.

ooops

Post by phpScott »

sorry, try this
INSERT INTO someTable (name, address, date) VALUES($name, $address, CURRENT_DATE());

phpScott
Post Reply