By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,985 Members | 2,907 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,985 IT Pros & Developers. It's quick & easy.

what function to use to convert date format in query?

P: n/a
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

Apr 17 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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
Apr 17 '06 #2

P: n/a
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

Apr 19 '06 #3

P: n/a
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

Apr 19 '06 #4

P: n/a
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

Apr 19 '06 #5

P: n/a
You're welcome, and good luck!

Jana

Apr 19 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.