2. PHPRunner General questions
  3. trouble with date-fromats
This topic is locked

trouble with date-fromats

 		9/19/2005 11:48:07 AM
PHPRunner General questions
scuba author

here in Germany we usually use i.e. 19.09.2005 (shape 1) as standart date format. MySQL formats dates as 2005-09-19 (shape 2)and PHPRunner as 2005-9-19 (shape 3).

If I import a column of shape 1-dates with MySQL column set to varchar and PHPRunner configured as normal textfield I receive shape 1 dates. But when I add another date in phprunner-list I receive shape 3-dates. If I now need to sort dates the result obviously is incorrect since two date-shapes are mixed.

If I now change MySQL columnn to "date" with the imported shape 1-dates then those are completly wrong interpreted. 19.09.2005 becomes 20.09.2019.

If I change date-format in the Excel file to shape 2 and export it as text-file, all dates are formated back to shape 1 and will again be interpreted wrong.

If I change all imported dates manualle in phprunner-list I receive shape 3-dates. If I now export them they again are formated back to shape 1 in the text-file and will be wrong if I import them again.
This is slowly driving me nuts - has anyone expirienced those date-problems too?
Admin 9/20/2005

Hi,
date values should be stored in DATE fields. If you follow this rule you woudn't meet any problem you mentioned.
To import your existing data properly you can do the following:

  1. Import Excel data to VARCHAR field.
  2. Convert imported DD.MM.YYYY format values to YYYY-MM-DD format using following SQL query:
    update mytable set datefield=concat(substring(datefield,7,4),'-',substring(datefield,4,2),'-',substring(datefield,1,2))



where mytable and datefield are your actual table and field names.
3. Change datefield type to DATE.
scuba author 9/20/2005

thanks for the help, I'll try that <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=6047&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
scuba author 9/28/2005

Sergey,
I've tried what you wrote but still didn't work first!

1.1.2004 now became: 04-.2-1. !!!
I figured out that the actual problem was the field-formatation of the excel-file. Those where date-formated. The Problem with that is, that dates then are displayed correctly - also in Access and also in the textfile but the formatation somehow disturbes MySQL and it displays wired date-patterns. I've formatted all fields in the excel-file to text-format. Now the dates arn't "dates" but pure text and MySQL can handle them correctly. So just for the record: convert all fields in an excel-file you want to export to pure text-format!!!

<img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=6156&image=1&table=forumreplies' class='bbc_emoticon' alt=':D' />
thanks for your help anyway!!