Date Issue

Robb_Lake

I've got some dates in a MySQL database as '12/31/9999'. I've looked and tried but have not succesfully gotten those dates to convert in PHP for a website i'm building.

Any ideas?

devinemke

it looks like you are storing your dates as strings (VARCHAR or TEXT). don't do that. use mySQL's native Date and Time Types.

Robb_Lake

devinemke wrote:
it looks like you are storing your dates as strings (VARCHAR or TEXT). don't do that. use mySQL's native Date and Time Types.

Nope, I've got them as DATETIME in the mySQL database. I get the "Warning: date() [function.date]: Windows does not support dates prior to midnight (00:00:00), January 1, 1970" message when I do a

date('m/d/Y',strtotime($row['Out_of_Service']

So I think it may be something with that statement. This works for the other dates which are of reasonable years (2007, etc).

devinemke

don't use PHP to do the date formatting, use mySQL's Date and Time Functions (specifically the DATE_FORMAT() function).

Robb_Lake

Awsome, got it to work. Here is my final statement:

$result = mysql_query("SELECT Application, Description, Contact, Scope, Technical_Contact, Architect, egate_alias,
		DATE_FORMAT(In_Service, '%w/%m/%Y') as In_Service, DATE_FORMAT(Out_of_Service, '%w/%m/%Y') as Out_of_Service
		 FROM application_data_table ORDER BY 'Application' asc");

Key thing to note: Date_Format(...) as VARIABLE is really slick 🙂