Sagewire Logo

Format datetime with query?

8 Message(s) by 4 Author(s) originally posted in php sql


From: MattMika Date:   Thursday, August 23, 2007
Is there a way to format date time (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'd 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


From: Rik Date:   Thursday, August 23, 2007
wrote in message:

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'd 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-function s.html#function_date-format
--
Rik Wasmus


From: mattmika Date:   Thursday, August 23, 2007
On Fri, 24 Aug 2007 03:17:15 +0200, Rik <luiheidsgoeroe@xxxxxxxxxxx>
wrote in message:

wrote in message:
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'd 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-functions.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 have tried so far returns
error s.

TIA


From: Captain Paralytic Date:   Friday, August 24, 2007
wrote in message:

| what I have tried so far returns errors.

Care to share the errors!


From: MattMika Date:   Friday, August 24, 2007
On Fri, 24 Aug 2007 01:54:24 -0700, Captain Paralytic
wrote in message:

wrote in message:
| what I have 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


From: Rik Date:   Friday, August 24, 2007
On Fri, 24 Aug 2007 18:35:05 +0200, MattMika <mattmika@xxxxxxxxxxx> wrot=
e:

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

| what I have 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'd =

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


From: MattMika Date:   Friday, August 24, 2007
On Fri, 24 Aug 2007 18:46:55 +0200, Rik <luiheidsgoeroe@xxxxxxxxxxx>
wrote in message:

Huh?
'%b. %D, %Y' seems OK to me, why bother breaking it apart (which'd
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 wasn't paying very good attn. Since Im
not sending variables from Flash to the query it isn't needed. Thanks
for your help.
Matt Mika


From: Tom Date:   Friday, August 24, 2007
wrote in message...
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'd 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 MikaYou can use the DATE_FORMAT function to pull out the specific fields you needed.


As an example if I needed it as "MM-DD-YYYY", I'd use...

SELECT DATE_FORMAT(Date_Column, '%m-%d-%Y') from Table where .....Tom
--
NewsGuy Takes Usenet Cellular!
Download newsgroup MP3's to your Cell or PDA
Free Trial - http://newsguy.com/cell phone.htm



Next Message: Unique Image Query


Blogs related to Format datetime with query?

RE: JIRA Vault Plugin (created)
JCHART-50, Change Resolution Time and date of first response custom field format from "Date" to "Datetime", Thiago Rossato, Resolved. New Feature, JCHART-49, Status over time, Bettina Zucker, Open ...

ODBC Update statement + functions = broken?
I also tried convert('DATETIME', '.....'), and some other non date related functions, all exhibit identical behavior (work fine in select statements, but not in update, even when embedded in a select). Any fixes, workarounds, ideas?

Retranscription du chat sur Katmaï CPT (sql server 2008)
A: The main focus in performance improvement in SQL Server 2008 is on better query optimization and execution on a single SMP system. Given the rapid advances in multi-core processor technology, we think this will satisfy the ...

embedded sql
... asp net sql basic php sql interface dts sql server ms sql 2000 sql insert jsp query optimization in oracle 9i vs sql server doesn"t violate query analyzer sql 2000 impact on server the rights sql timediff statement of formatdatetime ...

sql server 2005 training
formatdatetime sql command list sql dts login failed for user nt authorityanonymous postgres sql クエリ sql web site hosting sql server tools review sql runtime rollup in sql server resume and visual basic and sql server and nc ...

Convert Internet History Date to MySQL Date
//since the original format of the field isnt compatible with datetime, we now can alter the table to make it so $sql = “ALTER TABLE `$dbname`.`$dbtable` MODIFY COLUMN `Date` DATETIME NOT NULL, ENGINE = MyISAM”; mysql_query($sql); ...


Programming | Sports | Autos

copyright 2006
Valid XHTML 1.0 Transitional