Format datetime with query?

Is there a way to format datetime (2007-08-23 07:00:00) into something
like Aug. 23rd, 2007 7am within a query?

A little explanation. This is an events table that is queried from a
PHP class triggered from flash via AMFPHP, the mysql result is then
sent back to Flash.

I plan of having php forms to add, edit and delete entries in this
table so was thinking I could just have a datetime field and a
format_date field that would get a formatted version of the datetime
when inserted or updated.

Theres got to be a better solution to avoid having two date fields in
my db. Any suggestions?

TIA
Matt Mika

Re: Format datetime with query?

On Fri, 24 Aug 2007 01:23:53 +0200, MattMika wrote:

> Is there a way to format datetime (2007-08-23 07:00:00) into something
> like Aug. 23rd, 2007 7am within a query?
>
> A little explanation. This is an events table that is queried from a
> PHP class triggered from flash via AMFPHP, the mysql result is then
> sent back to Flash.
>
> I plan of having php forms to add, edit and delete entries in this
> table so was thinking I could just have a datetime field and a
> format_date field that would get a formatted version of the datetime
> when inserted or updated.
>

Is it MySQL? If so:
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functio ns.html#function_date-format
--
Rik Wasmus

Re: Format datetime with query?

On Fri, 24 Aug 2007 03:17:15 +0200, Rik
wrote:

>On Fri, 24 Aug 2007 01:23:53 +0200, MattMika wrote:
>
>> Is there a way to format datetime (2007-08-23 07:00:00) into something
>> like Aug. 23rd, 2007 7am within a query?
>>
>> A little explanation. This is an events table that is queried from a
>> PHP class triggered from flash via AMFPHP, the mysql result is then
>> sent back to Flash.
>>
>> I plan of having php forms to add, edit and delete entries in this
>> table so was thinking I could just have a datetime field and a
>> format_date field that would get a formatted version of the datetime
>> when inserted or updated.
>>
>
>Is it MySQL? If so:
>http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functi ons.html#function_date-format

Yes it is MySQL5. I didnt explain very well. I have a datetime field
that is populated in the db. Heres the query I have:

"SELECT events_date as Date, events_title as Event, events_description
as Description FROM events ORDER BY Date ASC"

What I am wondering is can I select that datetime field and format it
in a query.

Something like:

"SELECT date(events_date, '%b %a %Y') as Date, events_title as Event,
events_description as Description FROM events ORDER BY Date ASC"

I havent spent a WHOLE lot of time looking yet, but it doesnt look
like something that can be done as what I've tried so far returns
errors.

TIA

Re: Format datetime with query?

On 24 Aug, 03:51, mattm...@hotmail.com wrote:

| what I've tried so far returns errors.

Care to share the errors!

Re: Format datetime with query?

On Fri, 24 Aug 2007 01:54:24 -0700, Captain Paralytic
wrote:

>On 24 Aug, 03:51, mattm...@hotmail.com wrote:
>
>| what I've tried so far returns errors.
>
>Care to share the errors!

Ok, I uncommentd trace(fe.fault.faultstring) in my actionscript and
the error turned out to be too few parameters to sprintf.

I have this working in a test php file:
$sql = "SELECT DATE_FORMAT(events_date, '%b.' ' %D,' ' %Y') as Date,
events_title as Event, events_description as Description FROM events
ORDER BY Date ASC";

So now I know, yes, I can do what I was looking to do.

My original query in the php class for amfphp is this:
$sql = sprintf("SELECT format_date as Date, events_title as Event,
events_description as Description FROM events ORDER BY Date ASC");

This works fine.

My new query in the php class is this:
$sql = sprintf("SELECT DATE_FORMAT(events_date, '%b.' ' %D,' ' %Y') as
Date, events_title as Event, events_description as Description FROM
events ORDER BY Date ASC");

The DATE_FORMAT() is somehow messing with the sprintf so I need to
figure out why and Im good. Off to find my solution!

Thanks
Matt Mika

Re: Format datetime with query?

On Fri, 24 Aug 2007 18:35:05 +0200, MattMika wrot=
e:

> On Fri, 24 Aug 2007 01:54:24 -0700, Captain Paralytic
> wrote:
>
>> On 24 Aug, 03:51, mattm...@hotmail.com wrote:
>>
>> | what I've tried so far returns errors.
>>
>> Care to share the errors!
>
> Ok, I uncommentd trace(fe.fault.faultstring) in my actionscript and
> the error turned out to be too few parameters to sprintf.
>
> I have this working in a test php file:
> $sql =3D "SELECT DATE_FORMAT(events_date, '%b.' ' %D,' ' %Y') as Date,=

> events_title as Event, events_description as Description FROM events
> ORDER BY Date ASC";
>
> So now I know, yes, I can do what I was looking to do.
>
> My original query in the php class for amfphp is this:
> $sql =3D sprintf("SELECT format_date as Date, events_title as Event,
> events_description as Description FROM events ORDER BY Date ASC");
>
> This works fine.
>
> My new query in the php class is this:
> $sql =3D sprintf("SELECT DATE_FORMAT(events_date, '%b.' ' %D,' ' %Y') =
as

Huh?
'%b. %D, %Y' seems OK to me, why bother breaking it apart (which would =

result in an error in mysql).

> Date, events_title as Event, events_description as Description FROM
> events ORDER BY Date ASC");
>
> The DATE_FORMAT() is somehow messing with the sprintf so I need to
> figure out why and Im good. Off to find my solution!

Use %% for every % in the date format string.

$sql =3D sprintf("SELECT DATE_FORMAT(events_date, '%%b. %%D, %%Y') as Da=
te, =

events_title as Event, events_description as Description FROM events ORD=
ER =

BY Date ASC");

Then again, if this is your real statement, why even bother with the =

sprintf()? Only usefull for inserting/updating/searching on 'unknown' =

variables.
-- =

Rik Wasmus

Re: Format datetime with query?

On Fri, 24 Aug 2007 18:46:55 +0200, Rik
wrote:

>Huh?
>'%b. %D, %Y' seems OK to me, why bother breaking it apart (which would
>result in an error in mysql).
>
>> Date, events_title as Event, events_description as Description FROM
>> events ORDER BY Date ASC");
>>
>> The DATE_FORMAT() is somehow messing with the sprintf so I need to
>> figure out why and Im good. Off to find my solution!
>
>Use %% for every % in the date format string.
>
>$sql = sprintf("SELECT DATE_FORMAT(events_date, '%%b. %%D, %%Y') as Date,
>events_title as Event, events_description as Description FROM events ORDER
>BY Date ASC");
>
>Then again, if this is your real statement, why even bother with the
>sprintf()? Only usefull for inserting/updating/searching on 'unknown'
>variables.

I am taking the sprintf out, I was not paying very good attn. Since Im
not sending variables from Flash to the query it is not needed. Thanks
for your help.
Matt Mika

Re: Format datetime with query?

Post removed (X-No-Archive: yes)