Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Dates

  1. #1
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default Dates

    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.

  2. #2
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Yes, but it's neater to use MySQL. Do a mysqldump, delete the database, make an adjustment to the dump, and execute it.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends français | entiendo español | tôi ít hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  3. #3
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    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.

  4. #4
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,876
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    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).
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends français | entiendo español | tôi ít hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  5. #5
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    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.

  6. #6
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by Twey
    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

  7. #7
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    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.

  8. #8
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    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
    Last edited by NXArmada; 05-22-2006 at 02:46 PM.

  9. #9
    Join Date
    Dec 2004
    Location
    UK
    Posts
    2,358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    Quote Originally Posted by NXArmada
    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

  10. #10
    Join Date
    Apr 2006
    Posts
    190
    Thanks
    3
    Thanked 7 Times in 7 Posts

    Default

    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.
    Last edited by NXArmada; 05-22-2006 at 05:11 PM.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •