Error in MySQL Online Reference?

[Phil Nospam]

Hello all,

I was browsing through the reference manual on the mysql.com web site and
found the following code examples for some date/time functions:

mysql> SELECT STR_TO_DATE('00/00/0000', '%m/%d/%Y');
-> '0000-00-00'

mysql> SELECT STR_TO_DATE('04/31/2004', '%m/%d/%Y');
-> '2004-04-31'

In each example above the first line is the code the second is the result.
Do these look backwards to anyone else? I'll admit I'm a newbie that's
browsing the boards and trying to decide if I want to learn MySQL and PHP,
but to me the following makes more sense:

mysql> SELECT STR_TO_DATE('2004-04-31', '%m/%d/%Y');
-> '04/31/2004'

The online manual's example would indicate that the "format" portion of the
function (%m/%d/%Y) should be specifying the format of the INPUT string.
I'm thinking that the "format" portion of the function should be specifying
the RESULT format as in my example (based upon my experience with Oracle and
Brio).

So, do I have a lot to learn or are the first two examples incorrect?

Thanks,

Phil

[Aggro]

Phil Nospam wrote:
,

but to me the following makes more sense:

mysql> SELECT STR_TO_DATE('2004-04-31', '%m/%d/%Y');
-> '04/31/2004' So, do I have a lot to learn or are the first two examples incorrect?

Examples are correct and you still got lot to learn.

If you want to convert str_to_date (String to date), you have a string
and the result will be a date. MySQL saves the dates in yyyy-mm-dd
format, so the format of result is always the same, unless you use a
specific function to convert the date into string, that looks like a
date in different format.

So the second value for str_to_date is the format of the first
parameter, because without it, MySQL couldn't know for example what does
user mean by "1/2/2005" (In Finland, that is 2005-02-01, but in USA that
is 2005-01-02), and that kind of a function wouldn't be very usefull.

If you want to convert MySQL date into string, you can use
DATE_FORMAT(date,format)

For example:
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'

You can of course get results like "10/04/1997" out from it. The example
just demonstrates that the result is not a date, it is a string.

Summary:
So two different functions. Other changes string to date, and other date
to string.

[Bill Karwin]

Phil Nospam wrote:

So, do I have a lot to learn or are the first two examples incorrect?

Well, one thing you could learn is to try the query yourself against the
test database. Empirical evidence is better than a thousand words:

mysql> SELECT STR_TO_DATE('2004-04-31', '%m/%d/%Y');
-> NULL
mysql> SELECT STR_TO_DATE('2004-04-31', '%Y/%m/%d');
-> NULL
mysql> SELECT STR_TO_DATE('2004-04-31', '%Y-%m-%d');
-> '2004-04-31'

Looks like the second argument is indeed the _input_ format specifier.

Regards,
Bill K.