MySQL 4 vs. MySQL 5: str_to_date 1

[Ghodmode]

The following query runs fine on my test server at home, running MySQL 5.0.15, but on my hosting provider's server, running MySQL 4.0.24, it gets a syntax error. Can anyone help me figure out why?

insert into product (name, url, cost, purchase_dt)
values('Whatchamacallit', '[URL unfurl="true"]http://www.whatchamacallit.com',[/URL] 50000, str_to_date('2006-04-20', '%Y-%m-%d'))

Here's the error message :

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '('2006-04-20', '%Y-%m-%d'))' at line 2

Thank you

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[TonyGroves]

I don't know the STR_TO_DATE function at all. Are you trying to store '2006-04-20' into a DATE field? If so, then you don't need to convert it at all, the string '2006-04-20' is perfectly acceptable for a DATE field.

 

[Ghodmode]

It's necessary because of dates which are not so clear. For example, if the date is April 5th: 2006-04-05, the default date format in some places might make that May 4th.

STR_TO_DATE(str,format)

This is the inverse of the DATE_FORMAT() function. It takes a string str and a format string format. STR_TO_DATE() returns a DATETIME value if the format string contains both date and time parts, or a DATE or TIME value if the string contains only date or time parts.

Ref:
MySQL 4 Reference Manual & MySQL 5 Reference Manual

Thanks.

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[TonyGroves]

STR_TO_DATE() is available as of MySQL 4.1.1.

From

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

 

[Ghodmode]

Jackpot! That's the answer! I'm using 4.0. I should've read more carefully. Thanks, Tony... Bummer... So, How?... I guess I'll have to convert the date to some "default" format in my Web app before putting it into the insert statement.

Thanks.

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[r937]

It's necessary because of dates which are not so clear. For example, if the date is April 5th: 2006-04-05, the default date format in some places might make that May 4th.

which places?


in my experience, yyyy-xx-yy is always interpreted with xx=mm and yy=dd


r937.com | rudy.ca

 

[Ghodmode]

Specifically, here in Malaysia, the day is always represented before the month. If a Malaysian were to see 2006-04-05, they would automatically assume that it was May 4th. I assumed that it was the same in England since Malaysia follows England.

Generally, I just wish to be thorough.

--
-- Ghodmode

Give a man a fish and he'll come back to buy more... Teach a man to fish and you're out of business.

 

[r937]

but the default format in mysql (and there is only one format, which also happens to be the ISO standard) will never interpret 2006-04-05 as May 4th

so the problem is up to you, to reformat malaysian 2006-04-05 into standard 2006-05-04 for mysql, before passing the value to mysql...

... or else use STR_TO_DATE

r937.com | rudy.ca