mysql inserts with dates

nicki

Does anyone have a resource for inserting date_format() dates into a table that is NOT the mysql manual.

i have inconsistent results- i finally got this to insert properly, i don't know why i need to function it twice, but it works
insert into table(somedate) values(
date_format(date_format('$new_wed_date','%m/%d/%y'),'%m/%d/%y'))

however, this does not.... and neither does the below. i guess the rule is not to function it twice. i see no rules.

insert into meeting_info(start_date)
values(date_format(date_format('9/5/2002','%m/%d/%Y'),'%m/%d/%Y'))

insert into meeting_info(start_date)
values(date_format('9/5/2002','%m/%d/%Y'))

nicki

sorry- forgot to fill that variable
$new_wed_date is 9/5/02 with only a 2 digit year

Anon

hi nicki,

so your problem is fixed??

however, you don't need to do the funny
2*date_format (guess it's for select only):

mysql expects dates as Year+Month+Day
- so if there's no problem you could
place the year at the first postion like
02/01/31 for the '31th of January 2002' and
just do an insert:
insert into table_x values('02/01/31');
mysql likes this ;-)

hth
chris

[deleted]

As chris said, date_format expects a MYSQL date, not a custom date.

So giving it '9/5/2002' will never work because it expects '2002/5/9' (or '2002/9/5' depending on where in the world you are.

A good urle is to use your script to change the date format to something that your database understands instead of making the database understand the format that you use.

read about strtotime() and strftime()

(or use postgreSQL which will understand just about any time/date format you can think of)

nicki

Well, i'm coming from oracle which converts absolutely any *&^%& you give it into a date. i'm really surprised that mysql doesn't but i guess that makes it light and quick. however, it did work - the first statement. i don't know why but it inserts a proper date when you do the function twice only when using the 2 digit date. i swear to god. but oracle often forces you to use the date function twice which is why i tried it in the first place. strange. i decided to convert everything to yyyy-mm-dd anyway, so ....