what function to use to convert date format in query?

[dubing]

Hello everyone,

There is a field of DATETIME type in our Access 2000 database. PHP is
used to query the data and display the query results on the web. Does
Access provide any function that can convert DATETIME field into a more
user friendly format directly in query? I can do the following in
MySQL. How should it be done in Access?

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

It returns 'Saturday October 1997''.

Thanks,

Bing

[Rick Brandt]

du****@gmail.com wrote:

Hello everyone,

There is a field of DATETIME type in our Access 2000 database. PHP is
used to query the data and display the query results on the web. Does
Access provide any function that can convert DATETIME field into a
more user friendly format directly in query? I can do the following
in MySQL. How should it be done in Access?

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

It returns 'Saturday October 1997''.

Format([DateFieldName], "dddd mmmm yyyy")

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[dubing]

Rick Brandt wrote:

du****@gmail.com wrote:

Hello everyone,

There is a field of DATETIME type in our Access 2000 database. PHP is
used to query the data and display the query results on the web. Does
Access provide any function that can convert DATETIME field into a
more user friendly format directly in query? I can do the following
in MySQL. How should it be done in Access?

SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');

It returns 'Saturday October 1997''.

Format([DateFieldName], "dddd mmmm yyyy")

Thanks for the response. The date got formated correctly in the result
set. However the name of the field got changed.

The following PHP code snippet is used to return the name of the field:

===
/* Show fields' names in the result set*/

while( ($f = odbtp_fetch_field( $qry_getdetail )) ) {
echo "<b>". $f->name ." (" . $f->type . ") , </b>";
}
===

If the query is:

$qry_getdetail = odbtp_query("SELECT end_date FROM qryProjectDetail");

The result is: end_date (DATETIME). And the value of 'end_date'
returned is 'Object'.

If the query is:

$qry_getdetail = odbtp_query("SELECT FORMAT(end_date, 'mmmm d yyyy')
FROM qryProjectDetail");

The name of the field and its type changed to be: Expr1000 (VARCHAR).
And the value of 'end_date' is correct.

Why is that? I need both the field's name and value to be correct.

Thanks,

Bing

[Jana]

Bing:

The Format function converts a date to a string, formatted in the way
you specify, which is why you're getting a different data type. If you
need BOTH the field for your PHP and the nicely formatted text, I would
suggest that you add the end_date field back to your query. You can
rename the format results by replacing the Expr1000 with text_end_date
in your SQL. So you'll have SELECT (Format([end_date],"dddd mmmm
yyyy")) AS text_end_date instead of SELECT (Format([end_date],"dddd
mmmm yyyy")) AS Expr1000. Use the end_date whenever you need the
datetime value, use the text_end_date when you need to display it for
the user.

HTH,
Jana

[dubing]

Jana wrote:

Bing:

The Format function converts a date to a string, formatted in the way
you specify, which is why you're getting a different data type. If you
need BOTH the field for your PHP and the nicely formatted text, I would
suggest that you add the end_date field back to your query. You can
rename the format results by replacing the Expr1000 with text_end_date
in your SQL. So you'll have SELECT (Format([end_date],"dddd mmmm
yyyy")) AS text_end_date instead of SELECT (Format([end_date],"dddd
mmmm yyyy")) AS Expr1000. Use the end_date whenever you need the
datetime value, use the text_end_date when you need to display it for
the user.

Thanks a lot for the help, Jana. That makes sense. I forgot field's
name could be customized using AS.

Bing

[Jana]

You're welcome, and good luck!

Jana