Date format

**Christian Sagmueller** · Jul 17 '05, 12:02 PM

Re: Date format

Am Sat, 12 Mar 2005 17:48:32 +0000 schrieb Rick:
[color=blue]
> I am going crazy on how to format a simple date. I have this in my MySQL
> table. "2005-03-10 08:44:21" and I want it to format out like "3/10/2005" or
> "03/10/05" but I seem to be going at it the wrong way. Here what I have but
> wow I get a wrong date.
>
> $cdate = date("r",$cdate );[/color]
try `$cdate = date("m/d/y",$cdate);'

Christian.

--

http://sagmueller.net

**Andy Hassall** · Jul 17 '05, 12:02 PM

Re: Date format

On Sat, 12 Mar 2005 17:48:32 GMT, "Rick" <rick@di-wave.com> wrote:
[color=blue]
>I am going crazy on how to format a simple date. I have this in my MySQL
>table. "2005-03-10 08:44:21" and I want it to format out like "3/10/2005" or
>"03/10/05" but I seem to be going at it the wrong way. Here what I have but
>wow I get a wrong date.
>
>$cdate = date("r",$cdate );[/color]

date() works on UNIX timestamps, not string representations of dates.

Either fetch a UNIX timestamp from MySQL (I think it's TO_UNIXTIME or
similar), or get MySQL to format it for you using DATE_FORMAT in the SQL
statement.

--
Andy Hassall / <andy@andyh.co. uk> / <http://www.andyh.co.uk >
<http://www.andyhsoftwa re.co.uk/space> Space: disk usage analysis tool

**Jan Pieter Kunst** · Jul 17 '05, 12:02 PM

Re: Date format

Rick wrote:[color=blue]
> I am going crazy on how to format a simple date. I have this in my MySQL
> table. "2005-03-10 08:44:21" and I want it to format out like "3/10/2005" or
> "03/10/05" but I seem to be going at it the wrong way.[/color]

Let MySQL do the work.

First format: SELECT DATE_FORMAT(you r_date_field, '%e/%c/%Y') FROM ...
Second format: SELECT DATE_FORMAT(you r_date_field, '%d/%c/%y') FROM ...

fw_error_www

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

JP

--
Sorry, <devnull@cauce. org> is a spam trap.
Real e-mail address unavailable. 5000+ spams per month.

**Chris Hope** · Jul 17 '05, 12:02 PM

Re: Date format

Andy Hassall wrote:
[color=blue]
> On Sat, 12 Mar 2005 17:48:32 GMT, "Rick" <rick@di-wave.com> wrote:
>[color=green]
>>I am going crazy on how to format a simple date. I have this in my
>>MySQL table. "2005-03-10 08:44:21" and I want it to format out like
>>"3/10/2005" or "03/10/05" but I seem to be going at it the wrong way.
>>Here what I have but wow I get a wrong date.
>>
>>$cdate = date("r",$cdate );[/color]
>
> date() works on UNIX timestamps, not string representations of dates.
>
> Either fetch a UNIX timestamp from MySQL (I think it's TO_UNIXTIME or
> similar), or get MySQL to format it for you using DATE_FORMAT in the
> SQL statement.[/color]

Or you can convert the MySQL datetime value into a unix timestamp in PHP
using the strtotime() function
eg $cdate = date("r", strtotime($cdat e));

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

**Rick** · Jul 17 '05, 12:02 PM

Re: Date format

WOW that was easy thanks very much!!!

Thank you everyone that helped!!

Rick

"Chris Hope" <blackhole@elec trictoolbox.com > wrote in message
news:39h1o0F5ve grhU1@individua l.net...[color=blue]
> Andy Hassall wrote:
>[color=green]
>> On Sat, 12 Mar 2005 17:48:32 GMT, "Rick" <rick@di-wave.com> wrote:
>>[color=darkred]
>>>I am going crazy on how to format a simple date. I have this in my
>>>MySQL table. "2005-03-10 08:44:21" and I want it to format out like
>>>"3/10/2005" or "03/10/05" but I seem to be going at it the wrong way.
>>>Here what I have but wow I get a wrong date.
>>>
>>>$cdate = date("r",$cdate );[/color]
>>
>> date() works on UNIX timestamps, not string representations of dates.
>>
>> Either fetch a UNIX timestamp from MySQL (I think it's TO_UNIXTIME or
>> similar), or get MySQL to format it for you using DATE_FORMAT in the
>> SQL statement.[/color]
>
> Or you can convert the MySQL datetime value into a unix timestamp in PHP
> using the strtotime() function
> eg $cdate = date("r", strtotime($cdat e));
>
> --
> Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/[/color]

**Kenneth Downs** · Jul 17 '05, 12:02 PM

Re: Date format

Andy Hassall wrote:
[color=blue]
> On Sat, 12 Mar 2005 17:48:32 GMT, "Rick" <rick@di-wave.com> wrote:
>[color=green]
>>I am going crazy on how to format a simple date. I have this in my MySQL
>>table. "2005-03-10 08:44:21" and I want it to format out like "3/10/2005"
>>or "03/10/05" but I seem to be going at it the wrong way. Here what I have
>>but wow I get a wrong date.
>>
>>$cdate = date("r",$cdate );[/color]
>
> date() works on UNIX timestamps, not string representations of dates.
>
> Either fetch a UNIX timestamp from MySQL (I think it's TO_UNIXTIME or
> similar), or get MySQL to format it for you using DATE_FORMAT in the SQL
> statement.
>[/color]

or $cdate=date("r" ,strtotime($cda te));

strtotime is cool.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )

**Michael Fesser** · Jul 17 '05, 12:02 PM

Re: Date format

.oO(Kenneth Downs)
[color=blue]
>Andy Hassall wrote:
>[color=green]
>> Either fetch a UNIX timestamp from MySQL (I think it's TO_UNIXTIME or
>> similar), or get MySQL to format it for you using DATE_FORMAT in the SQL
>> statement.
>>[/color]
>or $cdate=date("r" ,strtotime($cda te));
>
>strtotime is cool.[/color]

DATE_FORMAT() is cooler.

Micha

**Kenneth Downs** · Jul 17 '05, 12:03 PM

Re: Date format

Michael Fesser wrote:
[color=blue]
> .oO(Kenneth Downs)
>[color=green]
>>Andy Hassall wrote:
>>[color=darkred]
>>> Either fetch a UNIX timestamp from MySQL (I think it's TO_UNIXTIME or
>>> similar), or get MySQL to format it for you using DATE_FORMAT in the SQL
>>> statement.
>>>[/color]
>>or $cdate=date("r" ,strtotime($cda te));
>>
>>strtotime is cool.[/color]
>
> DATE_FORMAT() is cooler.
>
> Micha[/color]

Yeah, but its a DB function.

<opinion category="mild rant">
My own tastes run to having PHP as the middleman do all of the formatting.
In the one direction PHP takes user input and turns it into formatted SQL
writes (insert, update, etc) and in the other direction it takes db-native
data from a SELECT and formats it for human consumption.

If you have a data dictionary, you write two simple functions that take care
of all of this for you:

function SQL_FORMAT($use r_value,$type) // makes SQL-acceptable strings...
function HTML_FORMAT($sq l_value,$type) // makes user-readable strings...

Where $type is pulled from the dd. If different sites and/or users want
different date display styles, you can have HTML_FORMAT read a preferences
file.

While there is nothing "wrong" with DATE_FORMAT(), it would muddy this clean
placement of responsibilitie s.
</opinion>

but of course to each his own.

--
Kenneth Downs
Secure Data Software, Inc.
(Ken)nneth@(Sec )ure(Dat)a(.com )

**Geoff M** · Jul 17 '05, 12:03 PM

Re: Date format

knode.wants.thi s@see.sigblock says...[color=blue]
> Michael Fesser wrote:[color=green]
> > DATE_FORMAT() is cooler.[/color]
>
> Yeah, but its a DB function.
>
> <opinion category="mild rant">
> My own tastes run to having PHP as the middleman do all of the formatting.[/color]
<snip>[color=blue]
> </opinion>
>
> but of course to each his own.[/color]

<alt_opinion category="mild rant">
In general, the majority (as I mainly work with Oracle this can read
*overwhelming, approaching total* majority) of functions which can be
executed at both the database and application level will run *much* more
efficiently at the database level.

Unless you have a critical need for database vendor obfuscation (and
therefore accept that you will be producing less than optimal code), my
rule is:
"if it can be done by the database, let the database do it".
</alt_opinion>

Geoff M

**Chris Hope** · Jul 17 '05, 12:03 PM

Re: Date format

Geoff M wrote:
[color=blue]
> knode.wants.thi s@see.sigblock says...[color=green]
>> Michael Fesser wrote:[color=darkred]
>> > DATE_FORMAT() is cooler.[/color]
>>
>> Yeah, but its a DB function.
>>
>> <opinion category="mild rant">
>> My own tastes run to having PHP as the middleman do all of the
>> formatting.[/color]
> <snip>[color=green]
>> </opinion>
>>
>> but of course to each his own.[/color]
>
> <alt_opinion category="mild rant">
> In general, the majority (as I mainly work with Oracle this can read
> *overwhelming, approaching total* majority) of functions which can be
> executed at both the database and application level will run *much*
> more efficiently at the database level.
>
> Unless you have a critical need for database vendor obfuscation (and
> therefore accept that you will be producing less than optimal code),
> my rule is:
> "if it can be done by the database, let the database do it".
> </alt_opinion>[/color]

And even if you have a preferences file as in Kenneth's case you could
still use it to format the date in the SQL statement, since you are
creating the statement in PHP.

I've always tended to do all the formatting in PHP but your argument
does make sense as it should presumably be more efficient for the
database server to format the date and time, considering it has to
anyway in order to get it into whatever format it spits out by default.

--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/

**Michael Fesser** · Jul 17 '05, 12:04 PM

Re: Date format

.oO(Chris Hope)
[color=blue]
>And even if you have a preferences file as in Kenneth's case you could
>still use it to format the date in the SQL statement, since you are
>creating the statement in PHP.[/color]

Yep. Additionally that avoids the use of Unix timestamps, which have
their own limitations.

Micha

Date format

Date format

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment