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);
...