Date Format PHP mysql

[alexgv14]

I have been trying to format a date that I receive from a value in a mysql database. The format in mysql is as follows.

1986-08-02

I want to convert that to

Aug. 2, 1986

I tried the date function in php and it was not working for me. Anyone, have any ideas how I can do this.

Thanks,

Alex

[Josh Connerty]

I have been trying to format a date that I receive from a value in a mysql database. The format in mysql is as follows.

1986-08-02

I want to convert that to

Aug. 2, 1986

I tried the date function in php and it was not working for me. Anyone, have any ideas how I can do this.

Thanks,

Alex

Is it possable to change the way in wich you add the dates to the database as keeping them in UNIX timestamps makes the whole date thing much much easier and better yet allows you to do more to it.

Ok if this isn't possable then you want to look into the explode function and the case statements as this will make up most of the function you need to make.

This should put you on the right track.

<?php

$date = 1986-08-02;

//We need to seperate the different parts YYYY-MM-DD
//We use the explode method for this we need to eplode the - chars
//So we need to create a variable holding our explode function
$parts = explode( "-" , $date );

//Now we should have the following
echo $parts[0] // 1986
echo $parts[1] // 08
echo $parts[2] // 02

// Now we create a case statement for each of the months!
switch( $parts[1] ) {
case 01:
$parts[1] = "Jan";
break;
case 02:
$parts[1] = "Feb";
break;
case 03:
$parts[1] = "Mar";
break;
case 04:
$parts[1] = "Apr";
break;
case 05:
$parts[1] = "May";
break;
case 06:
$parts[1] = "Jun";
break;
case 07:
$parts[1] = "Jul";
break;
case 08:
$parts[1] = "Aug";
break;
case 09:
$parts[1] = "Sep";
break;
case 10:
$parts[1] = "Oct";
break;
case 11:
$parts[1] = "Nov";
break;
case 12:
$parts[1] = "Dec";
break;

}

//Ok so now our $parts[1] (the month) is in the text format we can peice it back together
$newDate = $parts[1].". ".$parts[2].", ".$parts[0];

?>

Ok thats from the top of my head so it may need a little playing with, but it should be "Aug. 02, 1986". The only thing you may want to do is search the string for a 0 and if one exists replace it with "false".

To do this just add this line inder the name convertions for the months:
$parts[2] = str_replace( 0 , false , $parts[2] );

This should work you will have to test it out because as I said it is off the top of my head.

[martin5211]

If that is a DATETIME field type, you can use UNIX_TIMESTAMP() like this SELECT UNIX_TIMESTAMP(`date`) FROM `my_table` then use date() to show it correctly

[Josh Connerty]

I've checked this through and there is an error or two the one I can remember is the date at the top was classed as an integer and it needs to be a string.

So this is the working script:

<?php

$date = "1986-11-02";

//We need to seperate the different parts YYYY-MM-DD
//We use the explode method for this we need to eplode the - chars
//So we need to create a variable holding our explode function
$parts = explode( "-" , $date );

//Now we should have the following
// $parts[0] = 1986
// $parts[1] = 08
// $parts[2] = 02

// Now we create a case statement for each of the months!
switch( $parts[1] ) {
case 01:
$parts[1] = "Jan";
break;
case 02:
$parts[1] = "Feb";
break;
case 03:
$parts[1] = "Mar";
break;
case 04:
$parts[1] = "Apr";
break;
case 05:
$parts[1] = "May";
break;
case 06:
$parts[1] = "Jun";
break;
case 07:
$parts[1] = "Jul";
break;
case 08:
$parts[1] = "Aug";
break;
case 09:
$parts[1] = "Sep";
break;
case 10:
$parts[1] = "Oct";
break;
case 11:
$parts[1] = "Nov";
break;
case 12:
$parts[1] = "Dec";
break;

}

$parts[2] = str_replace( 0 , false , $parts[2] );

//Ok so now our $parts[1] (the month) is in the text format we can peice it back together
$newDate = $parts[1].". ".$parts[2].", ".$parts[0];

echo $date." is now ".$newDate;

?>

[Josh Connerty]

If that is a DATETIME field type, you can use UNIX_TIMESTAMP() like this SELECT UNIX_TIMESTAMP(`date`) FROM `my_table` then use date() to show it correctly

No if you were to add the UNIX timestamp to the database as a UNIX timestamp then you could use the array from the database like this:

echo date( "M. j, Y" , $databaseArray );

This would work if you used the PHP time function when putting the date in the database. Although there is a format you can use that PHP can convert to a timestamp then you can use the date function like I just did.

I will look at it some more. :-)

[Josh Connerty]

Try this:

strtotime( "1986-11-02" , time() );

[Josh Connerty]

Ok this is a working result:

$timeStamp = strtotime( "1986-11-02" , time() );
echo date( "M. j, Y" , $timeStamp );

[martin5211]

No if you were to add the UNIX timestamp to the database as a UNIX timestamp then you could use the array from the database like this:

echo date( "M. j, Y" , $databaseArray );

This would work if you used the PHP time function when putting the date in the database. Although there is a format you can use that PHP can convert to a timestamp then you can use the date function like I just did.

I will look at it some more. :-)

I was talking about MySQLDATETIME and DATE field, there is a MySQL function to convert directly to UNIX timestamp:

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

So, you can use my example mentioned above, the mysql result would be a timestamp compatible with PHP date()

Also, you can format the MySQL UNIX timestamp with FROM_UNIXTIME(), this way SELECT FROM_UNIXTIME(UNIX_TIMESTAMP(`date`), '%b. %e, %Y') FROM `my_table`

MySQL FROM_UNIXTIME(): http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_from-unixtime

MySQL Date Format:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html#function_date-format

[Josh Connerty]

I thought he was using dates in the past so it wouldn't work the same... Maybe not.

[alexgv14]

$timeStamp = strtotime( "1986-11-02" , time() );
echo date( "M. j, Y" , $timeStamp );

This worked fine for me. I was messing the strtotime part and just using the date function. Thanks for the help.

[BzzBee]

you can do this in my sql query as well
for example

select date_format(mydate,'%M. %d, %Y') as mydate from mytable; //mydate is supposed field name

you even dont need any php function but if you dont want to use mysql date function that you can use this function after fetching data from db.

$newdate = strtotime( $mydate );
echo date( "M. j, Y" , $newdate );

[Josh Connerty]

Good Good