BETWEEN and decimals...

[uprightdog]

Long story short - this is a excerpt of my SQL:

...AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '49.18351590991' AND '49.36369609009'...

The meta_value field is of the type LONGTEXT (hence the CAST). The value I should be retrieving is 49.2843030. However, nothing is retrieved. If I alter the query to the following, it does work:

...AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '49' AND '49.36369609009'...

What am I doing wrong?

[r937]

first impression: you are going to have a serious problem converting a 4 gigabyte text value to a single decimal number

second impression: if the CAST is successful, you should not be comparing a decimal number to strings

okay, now to business...

when you CAST AS DECIMAL without giving scale and precision, the default is DECIMAL(10,0)

in other words, no decimal places

does this answer your question?

[uprightdog]

first impression: you are going to have a serious problem converting a 4 gigabyte text value to a single decimal number

second impression: if the CAST is successful, you should not be comparing a decimal number to strings

I'm not sure what you're referring to by a 4 gigabyte text value.

The code I posted was actually automatically generated by WordPress, so I have limited control over it.

Regardless, yes, you answered my question, and allowed me to bypass the issue.

Cheers.