Date formatting

jesusdiscs

is it possible to change the formatting of a date retrieved from a mysql database, as the YYYY/MM/DD is (IMO) crap.

any help would be great as i'm gettin a tad annoyed now
thanks 😃

KoshNaranek

Just try substr([STRING],[StartPos],[LENGTH])
I've tried date(), mkdate() etc... substr is the simplest one 🙂

	if ($HTTP_ACCEPT_LANGUAGE=="de"){$dateFormat=substr($od,8,2).".".substr($od,5,2).".".substr($od,0,4)." ".substr($od,11,5);}

lurchblake

Not really sure what you mean but if you want to convert this date into a timestamp you can do a lot more with it.

$date_from_db="YYYY/MM/DD";

$split_data=split("/", $date_from_db);

$ts=mktime(0, 0, 0, $date_from_db[1], $date_from_db[2], $date_from_db[0]);

$new_date=date("d-m-Y", $ts);

This will display dd-mm-yyyy. For more information of how to format your date have a look at http://uk2.php.net/manual/en/function.date.php

Hope I was of help.

crowezr

I've found that using the date() function works very well. You should look it up on php.net to get the full array of options, but this is the basic format I use.

date("D, d M Y",(strtotime($row["date"])))

lurchblake

<?
$date_from_db="1999/12/21";

$split_data=split("/", $date_from_db);

$ts=mktime(0, 0, 0, $split_data[1], $split_data[2], $split_data[0]);

$new_date=date("d-m-Y", $ts);

echo $new_date."\n\n";
?>

But post before this, strtodate, is better way of doing things.

KoshNaranek

There are some problems with these...

in my MySQL-Version a datetime-field is stored like this:
2002-07-11 10:01:07

explode "/" 😕

I have not tried this on: date("D, d M Y",(strtotime($row["date"]))), have i?! Can't remember :rolleyes:

3 rows vs. 1 row... 😉 wich one is more simple? 😉 😉

lurchblake

Just for the record....
Explode and split would have the same effect in the case, and would use the same amount of code....

There are also other date fields such as date, user could even have a used a varchar with a formatted date placed in it. The best format is to store a timestamp rather than a datetime or date field. For the non-windows programmer timestamps are the way to go... Beware of false prophets.

KoshNaranek

a single timestamp-field is updated every time, you change the record... Hmmm... nice idea 😉 for my TroubleTicketSystem...

As we can see: there are some nice solutions...
just pick your favorite one 😃

i would use crowezr's

date("D, d M Y",(strtotime($row["date"])))

to prove it:
link to mysql-manual
...
The TIMESTAMP column type provides a type that you can use to automatically mark INSERT or UPDATE operations with the current date and time. If you have multiple TIMESTAMP columns, only the first one is updated automatically.
...

xblue

I'd recommend using DATE_FORMAT on date/datetime fields right within your select query (they do not auto-update though).

do a search for date_format here,
http://www.mysql.com/doc/en/Date_and_time_functions.html
it provides examples and lists all the formatting options.