format date on insert

[Yakisoba]

I have a little issue storing dates...

The dates are stored in the database as: yyyy-mm-dd

The dates are displayed on my webpage as simply: mm/dd

I accomplished this using DATE_FORMAT(dateValue, '%m/%d') in my SELECT statement.

now, the user has the option of changing the dd or mm and then save the changes. However, since the formats do not match the date is always 0000-00-00 (in the DB).

Is there are way to format the date in the INSERT statment, and somehow turn mm/dd into yyyy-mm-dd (yyyy=the current year) so that it will save properly?

Thanks

Yakisoba

[nst]

I think that you should do the convertion of mm/dd to the appropriate MySql format before the INSERT statement.

[Yakisoba]

I was hoping to incorperate the STR_TO_DATE function in my INSERT statement...Not sure how to do this yet.

Any suggestions?

Remember: the user cannot enter the year, I would like the year to be automatically entered as the current year.

Thanks

Yakisoba

Example:
INSERT INTO date_test (start_date) VALUES (STR_TO_DATE("12/15", "%m/%d"));
(this works however it gives me "0000-12-15". I would like to somehow place the current year in YYYY)

[guelphdad]

where are the values of mm and dd coming from? you have to save them as variables in order to pass them to your database right?
then why not just compose the year and the two variables into one string to enter into your database.

[Philipp]

i would aswell do the convertion before SELECT. i had a lot of troubles by doing everything in the select statement

[Yakisoba]

guelphdad - mm and dd ("mm/dd") are coming from a row in an html table.
The table is constructed through Javascript (and a little php). Once the save button is clicked the data/table is converted to CSV, then each row is split (using php "for loop") by \r\n, then each column is divided by ";"...

currently it works (i.e. information is stored in thier respective columns)...its just not liking my date format.

Not all the information in the table is a date, and there is no specific variable to differentiate the date from other fields. With that said, the date cells in the table do posses a type of date formatting (when the table is generated the date cells are givin a special ID. users are forced to enter mm/dd). Maybe I'll try to format the date to MySQL yyyy-mm-dd during the conversion to CSV.



Philipp - Currently the date conversion in my select statement runs beautifully...I'm hoping I won't have to change that.

Any suggestions?

Thanks,

Yak

[guelphdad]

With that said, the date cells in the table do posses a type of date formatting (when the table is generated the date cells are givin a special ID. users are forced to enter mm/dd).
There's your answer then isn't it? force them to enter yyyy/mm/dd :)

[Yakisoba]

not quite...

although I would like the user to enter the date I only want mm/dd to be displayed (kind of like excel - enter the whole date and only a specified format is displayed , the rest of the information is saved in the background)...

Now I am toying with the idea that, the user is forced to type in yyyy/mm/dd then via ajax (when the cell loses focus / onblur) the cell data is saved to the DB then reloaded via INSERT...when the data is reinserted I would use mySQL DATE_FORMAT().

Yakisoba

[guelphdad]

why go through all that bother to eliminate showing the year?

[Yakisoba]

I would like my form/cells to possess some excel-like features...Ideally I would like to use this as a daily reporting tool in which case at a glance the year is not important, as well, having the year repeated all over the screen (in every date field) will require me to make bigger fields and take up valuable screen real estate.
With that said, the year is an essential piece of data that must be stored for future queries.