MySQL Date Formatting - Garbled Date Insert

[SMRGroup]

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.

 

[sleipnir214]

MySQL's date format is YYYY-MM-DD. ______________________________________________________________________
TANSTAAFL!

 

[SMRGroup]

sleipnir214,

>MySQL's date format is YYYY-MM-DD.<

I don't follow... are you saying all dates in MySQL have to be yyyy/mm/dd? If so, that doesn't appear to be the case.

For example, a TIMESTAMP field is mm/dd/yyyy hh:mm:ss.

Or are you saying that just the DATE field datatype has to be yyyy/mm/dd? In which case, how does one enter any date information when the server convention is mm/dd/yyyy?


Stuart.

 

[sleipnir214]

I don't know where your timestamp information is coming from. On my system timestamp reports the information as YYYY-MM-DD HH:MM:SS.

Yes, you need to match the input MySQL expects. What server are you talking about that it's convention is mm/dd/yyyy? ______________________________________________________________________
TANSTAAFL!

 

[SMRGroup]

sleipnir214,

>I don't know where your timestamp information is coming from. On my system timestamp reports the information as YYYY-MM-DD HH:MM:SS.<

Not on any of mine - I've three MySQL installations running, one on XP and another on Windows 98 (both local network) and a third on my Windows 2000 web server. All three show TIMESTAMP as mm/dd/yyyy hh:mm:ss.

>What server are you talking about that it's convention is mm/dd/yyyy?<

This is Microsoft IIS running on Windows 2000. If the web server is in the US (which this is) then the default time format will be mm/dd/yyyy. You can check this by putting the <% Date %> tag in an ASP page, and control the display using the Session.LCID parameter, 1033 being US English.


Stuart.

 

[sleipnir214]

*sigh*

This from MySQL’s online documentation (

http://www.mysql.com/doc/en/Column_types.html):

[tt]DATE
A date. The supported range is '1000-01-01' to '9999-12-31'. MySQL displays DATE values in 'YYYY-MM-DD' format, but allows you to assign values to DATE columns using either strings or numbers. See section 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types.

DATETIME
A date and time combination. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'. MySQL displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format, but allows you to assign values to DATETIME columns using either strings or numbers. See section 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types.

TIMESTAMP[(M)]
A timestamp. The range is '1970-01-01 00:00:00' to sometime in the year 2037. MySQL displays TIMESTAMP values in YYYYMMDDHHMMSS, YYMMDDHHMMSS, YYYYMMDD, or YYMMDD format, depending on whether M is 14 (or missing), 12, 8, or 6, but allows you to assign values to TIMESTAMP columns using either strings or numbers. A TIMESTAMP column is useful for recording the date and time of an INSERT or UPDATE operation because it is automatically set to the date and time of the most recent operation if you don't give it a value yourself. You can also set it to the current date and time by assigning it a NULL value. See section 6.2.2 Date and Time Types. The M argument affects only how a TIMESTAMP column is displayed; its values always are stored using 4 bytes each. Note that TIMESTAMP(M) columns where M is 8 or 14 are reported to be numbers while other TIMESTAMP(M) columns are reported to be strings. This is just to ensure that one can reliably dump and restore the table with these types! See section 6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types. [/tt]


This also from the online manual (

http://www.mysql.com/doc/en/DATETIME.html):

[tt]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][li] As a string with no delimiters in either 'YYYYMMDDHHMMSS' or 'YYMMDDHHMMSS' format, provided that the string makes sense as a date. For example, '19970523091528' and '970523091528' are interpreted as '1997-05-23 09:15:28', but '971122129015' is illegal (it has a nonsensical minute part) and becomes '0000-00-00 00:00:00'.[/li][li]As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '19970523' and '970523' are interpreted as '1997-05-23', but '971332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.[/li][li]As a number in either YYYYMMDDHHMMSS or YYMMDDHHMMSS format, provided that the number makes sense as a date. For example, 19830905132800 and 830905132800 are interpreted as '1983-09-05 13:28:00'.[/li][li]As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.[/li][li]As the result of a function that returns a value that is acceptable in a DATETIME, DATE, or TIMESTAMP context, such as NOW() or CURRENT_DATE.[/li][/ul][/tt]


It is obvious to me that ASP is doing some kind of reformatting of the data before it displays it. Do not depend on that transformation’s being two-way -- that can’t be depended on even when Microsoft has written both parts of the software, much less when you have Microsoft software interfacing with 3[sup]rd[/sup]-party software.

Give MySQL the data in the format it expects and see whether it is more likely to cooperate. ______________________________________________________________________
TANSTAAFL!

 

[SMRGroup]

sleipnir214,

No need to *sigh*, unless a show of disdain was your intent.

I'm familiar with the MySQL documentation, but as I mentioned earlier, my TIMESTAMP is not being entered in the YYYY-MM-DD HH:MM:SS format and neither is the DATE field, which is the whole point of this thread, but thank you for repeating all that information here.

None of it actually helps though, I’ve got a date in the mm/dd/yyyy format and need to get it into the database as a date without it being mangled, as was the case with Access 2002. There doesn’t seem to be an obvious solution to that. Displaying a date on an ASP page as 2002/10/11 is completely non-standard and will befuddle everyone who tries to use the page.


Stuart.

 

[sleipnir214]

The sigh designates nearing end of patience. Quit trying to find some Mi¢ro$oft automagic transform.

You are a programmer writing a program. Programmatically change the date from one format to the other as you need.

To display MySQL’s date in the format you need, do the following:
[ol][li]Pull off and keep the first four characters.[/li][li]Pull off and discard the next character.[/li][li]Append a slash[/li][li]Append the first four characters you took off in step one[/li][li]Display the string[/li][/ol]

To store an American-style date in MySQL:
[ol][li]Pull off and keep the last four characters[/li][li]Pull off and discard the new last character[/li][li]Prepend a slash[/li][li]Prepend the four characters you removed in step one[/li][li]Insert the string[/li][/ol] ______________________________________________________________________
TANSTAAFL!

 

[SMRGroup]

sleipnir214,

Thanks for your responses, but they've been no help whatsoever. Write a whole bunch of code to reformat the date in and out of the database. Never would have thought of that! Genius. I am not and never have expected a bundled Microsoft solution, the only MS aspect involved is the web server OS and the IIS, but I find it hard to believe such a simple operation need be so convoluted.

I'd suggest that if answering honest questions on-line taxes your patience that you don't bother in future. It'll make your life so much easier.


Stuart.

 

[sleipnir214]

You don’t expect an automagic transform, and you don’t want to write the transform. What other choice is there? Or did you just post to complain?

The transformation required is a simple string manipulation. In fact, you only have to write the transformation from American to MySQL -- MySQL has a builtin date_format() function which can be used in a SQL statement to return the date in the format you need.

And doesn’t ASP support the construct of user-defined functions or subroutines? Couldn’t you then write the transform once and reuse it anywhere you need to in your site? Perhaps defining it in global.asa in the document root of your web-site and using it where needed? ______________________________________________________________________
TANSTAAFL!

 

[Gelu]

Hi,
If you wish to use a date like DATE TYPE in MySQL, for sure is not the right format.The only format for date accepted by MySQL is what it's you already know.
IMHO :
I think you can create a CHAR(10) field to store the data in whatever format of date you wish.But become a little dificult if you wish to make some comparision between date.
In this situation you can use some functions string.
I.e:
a field(MyDATE) of CHAR(10)wich can store -&quot;10/10/2002&quot;.. up to...&quot;12/31/2002&quot;
If you wish to retrieve data between this dates you can use something like this :
select * from YOUR_TABLE where LEFT(MyDATE,2)>=10 and MID(MyDATE,4,2)>=10 and LEFT(MyDATE,2)<=12 and MID(MyDATE,4,2)<=31 and RIGHT(MyDATE,4)='2002'
Hope it's help.
Regards,
Gelu

 

[sleipnir214]

One caveat on that method....

Since the date is stored in MM-DD-YYYY format, sorting on that column will require that you jump through hoops using MySQL's string functions in any &quot;order by&quot; clause. ______________________________________________________________________
TANSTAAFL!

 

[dkpede]

Hi Stuart,

I think I had a similar problem - but I gave up reading this whole thread... but it seems no one mentioned the ODBC setting.

On the configure menu to the ODBC I tried setting &quot;dont use setlocal&quot; to true - and all these problems disappeared...