Mysql date format

[PRodgers4284]

Is there anyway of changing the way the date format is stored in mysql database from 0000/00/00 to 00/00/0000?

[kenrbnsn]

If you're using the date or datetime field type in mysql, the date is always stored as YYYY-MM-DD. You can change how it look when you display it either when you retrieve it or in PHP by using a combination of the date() and strtotime() functions.

 

Ken

[PRodgers4284]

If you're using the date or datetime field type in mysql, the date is always stored as YYYY-MM-DD. You can change how it look when you display it either when you retrieve it or in PHP by using a combination of the date() and strtotime() functions.

 

Ken

 

what would happen if u changed the date field in the database to varchar and made sure that the validation checks that the day is valid, is that a bad method to use?

[whit3fir3]

There are 2 ways to change the date and time....I prefer to do it in the SELECT statement as it put less load on the webserver and there is less coding that needs to be done.  To do it in the SELECT statement try the following:

 

date_format(FIELDNAME, '%m/%d/%Y - %h:%i:%s %p') as FIELDNAME

 

The other way is you can use the date function built into PHP and do date formatting that way as other people have suggested.

 

Thanks,

 

whit3fir3

[whit3fir3]

There are 2 ways to change the date and time....I prefer to do it in the SELECT statement as it put less load on the webserver and there is less coding that needs to be done.  To do it in the SELECT statement try the following:

 

date_format(FIELDNAME, '%m/%d/%Y - %h:%i:%s %p') as FIELDNAME

 

The other way is you can use the date function built into PHP and do date formatting that way as other people have suggested.

 

Thanks,

 

whit3fir3

 

Sorry I only included a small snipit for the SQL code.  The entire select statement would be something like the following:

 

SELECT FIELDNAME, FIELDNAME, FIELDNAME, date_format(FIELDNAME, '%m/%d/%Y - %h:%i:%s %p') as FIELDNAME FROM TABLENAME

 

Any place you see FIELDNAME is the name of a field in the SQL database.  The second in bold is how the Date and Time will be displayed.  You can adjust this to fit your needs.  Right now it will display as MM/DD/YYYY - 00:00:00 am/pm

 

Thanks,

 

whit3fir3

[Barand]

what would happen if u changed the date field in the database to varchar and made sure that the validation checks that the day is valid' date=' is that a bad method to use?[/quote']

 

If you store date as a string in "dd/mm/yyyy" or "mm/dd/yyyy", or any way but yyyy-mm-dd (or yyyymmdd) then it is totally useless in a database. You cannot compare dates, sort by date, select ranges of dates, use datetime functions....

 

Got the idea?

 

Use DATETIME or DATE type using the ISO format YYYY-MM-DD.

 

Alternatively you could use INT and store as a Unix timestamp but this has 2 disadvantages

 

• If you use a tool like QueryBrowser or PHPMyAdimin to view the data they are totally unrecognisable
   
  
• Most MySQL datetime functions expect a DATE/DATETIME so you are forever having to convert
  

My 0.02

[PRodgers4284]

what would happen if u changed the date field in the database to varchar and made sure that the validation checks that the day is valid' date=' is that a bad method to use?[/quote']

 

If you store date as a string in "dd/mm/yyyy" or "mm/dd/yyyy", or any way but yyyy-mm-dd (or yyyymmdd) then it is totally useless in a database. You cannot compare dates, sort by date, select ranges of dates, use datetime functions....

 

Got the idea?

 

Use DATETIME or DATE type using the ISO format YYYY-MM-DD.

 

Alternatively you could use INT and store as a Unix timestamp but this has 2 disadvantages

 

• If you use a tool like QueryBrowser or PHPMyAdimin to view the data they are totally unrecognisable
   
  
• Most MySQL datetime functions expect a DATE/DATETIME so you are forever having to convert
  

My 0.02

 

 

Thanks for the reply, my code for the date validation is below, can i easily change the code to validate the date as 0000/00/00?

 

//DOB check) 

if (empty($dob)) {
//Set the error_stat to 1, which means that an error has occurred
$error_stat = 1;

//Set the message to tell the user to enter a dob
$dob_message = '*Please enter your date of birth*';
}

//Check the format and explode into $parts
  elseif (!ereg("^([0-9]{2})/([0-9]{2})/([0-9]{4})$", 
          $dob, $parts)){
$error_stat = 1; 	

//Set the message to tell the user the date is invalid
$dob_message = '*Invalid dob, must be DD/MM/YYYY format*';
}
    
  elseif (!checkdate($parts[2],$parts[1],$parts[3]))
  {
  $error_stat = 1; 
  
  //Set the message to tell the date is invalid for the month entered
$dob_message = '*Invalid dob, month must be between 1-12*';
}

elseif (intval($parts[3]) < 1948 || 
          intval($parts[3]) > intval(date("Y")))
  {
    
    $error_stat = 1; 

   //Set the message to tell the user the date is invalid for the year entered
$dob_message = '*Invalid dob, year must 1948 onwards*';
  }

[The Little Guy]

you could always set the database field to varchar and format using php, then It would be any way you would like.

[revraz]

But then you could never do any compares unless you convert it again.  Nothing wrong with storing it the right way and displaying it another.

[The Little Guy]

an even better way would be to use a unix timestamp. you can then convert it any way you would like in php

 

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_unix-timestamp

[sevun]

Could you still sort by the FIELDNAME column?

 

There are 2 ways to change the date and time....I prefer to do it in the SELECT statement as it put less load on the webserver and there is less coding that needs to be done.  To do it in the SELECT statement try the following:

 

date_format(FIELDNAME, '%m/%d/%Y - %h:%i:%s %p') as FIELDNAME

 

The other way is you can use the date function built into PHP and do date formatting that way as other people have suggested.

 

Thanks,

 

whit3fir3

 

Sorry I only included a small snipit for the SQL code.  The entire select statement would be something like the following:

 

SELECT FIELDNAME, FIELDNAME, FIELDNAME, date_format(FIELDNAME, '%m/%d/%Y - %h:%i:%s %p') as FIELDNAME FROM TABLENAME

 

Any place you see FIELDNAME is the name of a field in the SQL database.  The second in bold is how the Date and Time will be displayed.  You can adjust this to fit your needs.  Right now it will display as MM/DD/YYYY - 00:00:00 am/pm

 

Thanks,

 

whit3fir3

[revraz]

If it was DATE or DATETIME with YYYY-MM-DD yes, if it was VARCHAR and another format, no.

If it is INT and Unix, yes.