Dates

[NXArmada]

I have a Table in a MySQL db with dates corresponding to other data in the same Table.

The dates are stored in a VARCHAR in this format m/d/y (PHP FORMAT).

Is there a way I can display all records that have a date say greater than 03/31/06 using php?

This Table was made before I started working here and was not setup right for dates.

[Twey]

Yes, but it's neater to use MySQL. Do a mysqldump, delete the database, make an adjustment to the dump, and execute it.

[NXArmada]

MySQL likes to use this Date format 0000-00-00 which is YYYY-MM-DD if i set it up to use m/d/y in PHP and set the MySQL field to DATE will MySQL accept the PHP date format over its own?

I would like to do what I asked above just Temporary tell I get the Table fixed.

[Twey]

I believe it doesn't matter, MySQL will convert the mm/dd/yy format to its own. You merely need to change the column type(s).

[NXArmada]

I will give that a try Monday and let you know what happens. I think I have tried before and it converted all the dates in the Table to 0000-00-00 but than again I did not do a MySQL Dump.

I will defiantly give it a try.

[mwinter]

I believe it doesn't matter, MySQL will convert the mm/dd/yy format to its own.

It doesn't (see 11.3.1 in the MySQL reference manual). Some DBMS will if they're configured to use a locale, but basing date conversion on locale - which would be set to the server location, not necessarily that of the user - is still a bad idea, in my opinion.

Date input should use the international format (ISO 8601) unless the target audience will always know a specific local format (so that may preclude any foreigners). Even then, it would still be a good idea to convert it to the former internally (and you have no choice, here).

You could try modifying the data first, updating the format first to yyyy-mm-dd (notice the hyphens, rather than slashes) or yy-mm-dd, then following Twey's suggestion.

Mike

[NXArmada]

I am going to look into doing it the way MySQL is setup but I will have go through the Dump and change all the dates (3 per Record) to the MySQL format which will take some time, theres 1614 records and more added everyday.

[NXArmada]

I have been reading the MySQL 5.0 Reference Manual :: 11.3.1

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.

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.

According to that statement the PHP date format of m/d/y will work in a Date field as long all dates match. So I am going to change all the date in the records to match the PHP date format of m/d/y and see what happens.

If that doesn't work I will goto the MySQL version YYYY-MM-DD

[mwinter]

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.

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.

According to that statement the PHP date format of m/d/y will work in a Date field as long all dates match.

It doesn't say that at all. The reference to a 'relaxed' syntax means that it's not necessary to use a hyphen (-) to delimit date parts, or a colon (:) to delimit time parts; other punctuation characters can be used, instead (though it's not really recommended). The order of each component is still the same: year, month, day (and hour, minutes, seconds).

Mike

[NXArmada]

I kinda figured that.

After talking to the workers that use the program. They are all cool with using the date format provided by MySQL. They actually like that date format better so I am in the process of converting all the records to the MySQL date format.

Down side is I have 1613 records each with 12 date fields and I need to change them all.

Thanks Mike and Twey for your help.