Jump to content

Importing certain date format to mySQL problem


wilsonf1
 Share

Recommended Posts

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?

Link to comment
Share on other sites

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()

Edited by Haris S
Link to comment
Share on other sites

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?
Link to comment
Share on other sites

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];  }}

Edited by Haris S
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...