Guys,
I’ve recently converted a site from Access 2002 databases to MySQL, but am having an issue with MySQL’s date field.
On the site there is an insert record page consisting of two fields. One is a headline, the other a date. The default value in the date field is completed via the <% Date %> ASP instruction and the page contains <% Session.LCID = 1033 %> which formats the date according to the US convention of mm/dd/yyyy. The server is also set to use the US date conventions.
If we use today as an example, that means the default value for the field reads “10/10/2002”. Anyone submitting the form can change the default if they wish, provided the syntax is correct.
The datatype for the field is set to DATE in the insert record statement (Dreamweaver MX) as is the field in the MySQL database.
However, once submitted, the entry in the database reads “10/20/2010”. In other words, it’s nothing like what it should be. The day, date and month are all being confused by the MySQL database. Access 2002 before it accepted the data and formatted it correctly.
The question is; how do I fix this? I’ve tried all sorts of things, none of which have worked. The data must be submitted as a date as subsequent pages sort by date, so the data has to be formatted correctly.
Any pointers would be really appreciated.
Stuart.
I’ve recently converted a site from Access 2002 databases to MySQL, but am having an issue with MySQL’s date field.
On the site there is an insert record page consisting of two fields. One is a headline, the other a date. The default value in the date field is completed via the <% Date %> ASP instruction and the page contains <% Session.LCID = 1033 %> which formats the date according to the US convention of mm/dd/yyyy. The server is also set to use the US date conventions.
If we use today as an example, that means the default value for the field reads “10/10/2002”. Anyone submitting the form can change the default if they wish, provided the syntax is correct.
The datatype for the field is set to DATE in the insert record statement (Dreamweaver MX) as is the field in the MySQL database.
However, once submitted, the entry in the database reads “10/20/2010”. In other words, it’s nothing like what it should be. The day, date and month are all being confused by the MySQL database. Access 2002 before it accepted the data and formatted it correctly.
The question is; how do I fix this? I’ve tried all sorts of things, none of which have worked. The data must be submitted as a date as subsequent pages sort by date, so the data has to be formatted correctly.
Any pointers would be really appreciated.
Stuart.