Importing certain date format to mySQL problem

[wilsonf1]

Hi, I'm from the UK and am trying to import some ones spreadsheet to my mySQL database via phpmyadmin import feature.The date column looks like this: 1/1/2011 - very british! dd/mm/yyyyI set the database field to be: DATEWhen the import was over the dates were all set to 0000 00 I think.To fix it I changed his data to: 2011-1-1But I need to know if there's anyway I can leave the dates as 1/1/2011 as in the future he may send other spreadsheets in that format. Is it down to the data being compatible for mySQL or is there anything I can do my end to make his data work with mySQL?

[[dx]]

Use varchar for that instead of date.Personally, I'm using INT for storing time() value in format like this 1295794475.Later when selecting from DB, I use php's date() function.If you don't wont use time() or lets say must generate time() format from some date you can use mktime()

[wilsonf1]

Use varchar for that instead of date.Personally, I'm using INT for storing time() value in format like this 1295794475.Later when selecting from DB, I use php's date() function.

Hmmm are you sure, i've never not set a date column to DATE if that's what i'm working with. Will ordering by ASC or DESC be effected on a varchar field?

[[dx]]

Well, when you select data with: SELECT date FROM table WHERE time BETWEEN something AND something. Then you can use while loop and work with ASC or DESC.

// This will create timestamp for 23. January 2011.$start_today = mktime(0, 0, 0, 1, 23, 2011); $end_today = mktime(23, 59, 59, 1, 23, 2011);$query = mysql_query("SELECT * FROM table WHERE date BETWEEN '$start_today' AND '$end_today'") or die(mysql_error());if (mysql_num_rows($query) > 0) {  $r = mysql_fetch_array($query);  // ASCENDING  for ($i = 0; $i <= mysql_num_rows($query); $i++) {	echo $query[$i];  }    // DESCENDING  for ($i = mysql_num_rows($query); $i >= 0; $i--) {	echo $query[$i];  }}