STR_TO_DATE(str, format)

[atsea]

I have a form with a bunch of date fields (among others: name, address, etc)...

each date field accepts the date in the format mm/dd...

All the information on the form is saved to a DB...

My save (submit) feature works great except for the dates. Beacuse the format does not match the yyyy-mm-dd format. Currently dates are always stored as 0000-00-00.

Is there a way to format the date in the insert statement so that it can be stored properly in the DB?

Also, since the user cannot enter the year I would like to have the current year to be automatically entered. Is this possible?

I believe STR_TO_DATE is the answer, however I don't have anything working at the moment.

Thanks

atsea

 

[TonyGroves]

How about:
[tt]
STR_TO_DATE(
CONCAT(YEAR(CURDATE()),'/',$monthday),
'%Y/%m/%d'
)
[/tt]

 

[r937]

tony, STR_TO_DATE is not necessary if you construct the date string that way

r937.com | rudy.ca

 

[TonyGroves]

The input contains a slash, not a hyphen. There are of course other ways to do it, but that's one of the simplest, unless I'm missing something.

 

[r937]

mysql accepts '2006/05/08' as well as '2006-05-08'

r937.com | rudy.ca

 

[TonyGroves]

OK, that's the first I've heard of that.

 

[r937]

yeah, it's pretty wacky

You can specify DATETIME, DATE, and TIMESTAMP values using any of a common set of formats:
[ul][li]As a string in either 'YYYY-MM-DD HH:MM:SS' or 'YY-MM-DD HH:MM:SS' format. A "relaxed" syntax is allowed--any punctuation character may be used as the delimiter between date parts or time parts. For example, '98-12-31 11:30:45', '98.12.31 11+30+45', '98/12/31 11*30*45', and '98@12@31 11^30^45' are equivalent.[/li]
[li]As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A "relaxed" syntax is allowed here, too. For example, '98-12-31', '98.12.31', '98/12/31', and '98@12@31' are equivalent. [/li][/ul]

r937.com | rudy.ca