Date with 'Zeros' Output

[rakwel10]

I have a date picker that give me this result: e.g. 2011-29-08
When it is inserted in mysql, it outputs '0000-00000'.
What can I do?

I know "strftime("%Y-%m-%d %H:%M:%S", time());"
But I have no timestamp to put as an argument.
The only data I can use is the data that the date picker produces (yyyy-dd-mm)

Whats the proper thing to do?
I'll wait for your replies. Thanks!

[urtrivedi]

actually your format is yyyy-dd-mm

so change your strftime like

strftime("%Y-%d-%m", $datevariable);"

or change your date picker setting to yyyy-mm-dd, then you can directly insert date variable without strftime.

[Ichcha]

try

$insertDate  = date("Y-m-d",strtotime($date))

[rakwel10]

Thanks for you reply...

I still get zeros...

this is my code

$initial_date = mysql_prep($_POST['date']);
$date = strftime("%Y-%d-%m", $initial_date);

"$_POST['date']" has data = yyyy-dd-mm

Pls help. tnx

[Karthik_pranas]

better make your date picker code to show the date in YYYY-MM-DD forma

[rakwel10]

it does displays yyyy-dd-mm.

but when i insert it in mysql (the data type of the column is 'Date'),
it still returns zeros :(

[Karthik_pranas]

it does displays yyyy-dd-mm.

but when i insert it in mysql (the data type of the column is 'Date'), it still returns zeros :(

change the format to yyyy-mm-dd in your input

[urtrivedi]

I think you do not need mysql_prep

try following code without mysql_prep

$initial_date = $_POST['date'];

[rakwel10]

I did all what you've suggested...
I remove the mysql_prep, rearrange the month and day and others...
Still it didnt work...

I tested the value of "$_POST['date'];" before inserting in mysql.
The date alerted right (2011-08-29). Sadly, when inserted to mysql, zeros are still there...

Any other options? what do you think?
thanks for your help by the way..

[urtrivedi]

echo $query before execution and copy it and run in phpmyadmin.
also post that output query here.

[rakwel10]

Here is the query..

INSERT INTO tbl_tour_profile ( `tour_code`, `tour_name`, `subj_code`, `subj_name`, `section`, `subj_teacher`, `destination`, `date`, `path`, `tour_amount`, `expected_num_participants`, `estimated_tour_budget`)
VALUES ('10006', 'Tour MIS', 2122, '6MIS', 'N-201', ' Maria Allen J. Quiambao', 'HAU', 2011-08-31, 'tourdocs/10006_Doc1.doc','234','234','54756')

as you can see, the date format is right (yyy-mm-dd).
Is the problem in mysql?

[urtrivedi]

you must wrap around single quotes

....,'$initial_date',.....

[rakwel10]

initial_date is not part of the query coz its not in the field. Do you mean date?
...`subj_teacher`, `destination`, `date`,...

It does have quotes. Actually, I copied sql query in phpmyadmin.

[rakwel10]

in my desperate situation,
I tried changing the "date" datatype into a varchar or text.
The result is surprising and WEIRD...
It gave me a year - "1972".
Weird isnt it? Do you think mysql is the problem?

[Karthik_pranas]

put single quotes for the value of the date in the query

INSERT INTO tbl_tour_profile ( `tour_code`, `tour_name`, `subj_code`, `subj_name`, `section`, `subj_teacher`, `destination`, `date`, `path`, `tour_amount`, `expected_num_participants`, `estimated_tour_budget`)
VALUES ('10006', 'Tour MIS', 2122, '6MIS', 'N-201', ' Maria Allen J. Quiambao', 'HAU', '2011-08-31', 'tourdocs/10006_Doc1.doc','234','234','54756')

[diafol]

@KP - 1 for your patience!

[rakwel10]

KP,

the one with the red mark, thats the value of date field and it has quotes on it. what do you think?

INSERT INTO tbl_tour_profile ( `tour_code`, `tour_name`, `subj_code`, `subj_name`, `section`, `subj_teacher`, `destination`, `date`, `path`, `tour_amount`, `expected_num_participants`, `estimated_tour_budget`)
VALUES ('10006', 'Tour MIS', 2122, '6MIS', 'N-201', ' Maria Allen J. Quiambao', 'HAU', '2011-08-31', 'tourdocs/10006_Doc1.doc','234','234','54756')

[urtrivedi]

you need to quote around date and text, only numbers are allowed to write without qoutes

so
1) do not change your datatype
2) post your query (not output) only php mysql query with variables names, i will prepare query for you.

[rakwel10]

Here it is...

" INSERT INTO tbl_tour_profile ( `tour_code`, `tour_name`, `subj_code`, `subj_name`, 	 
`section`, `subj_teacher`, `destination`, `date`,  `path`, `tour_amount`, `expected_num_participants`, `estimated_tour_budget`) 
VALUES ('$tourId', '{$tourName}', {$classCode}, '{$subject}',  '{$section}', '{$teacher}', '{$destination}', {$date}, '{$path}','{$amount}','{$expected_num_participants}','{$estimated_tour_budget}')";

[Karthik_pranas]

" INSERT INTO tbl_tour_profile ( `tour_code`, `tour_name`, `subj_code`, `subj_name`,
    `section`, `subj_teacher`, `destination`, `date`, `path`, `tour_amount`, `expected_num_participants`, `estimated_tour_budget`)
    VALUES ('$tourId', '{$tourName}', {$classCode}, '{$subject}', '{$section}', '{$teacher}', '{$destination}', '{$date}', '{$path}','{$amount}','{$expected_num_participants}','{$estimated_tour_budget}')";