SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    BETWEEN and decimals...

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

    Code:
    ...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:

    Code:
    ...AND CAST(mt1.meta_value AS DECIMAL) BETWEEN '49' AND '49.36369609009'...
    What am I doing wrong?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,014
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot
    Join Date
    Dec 2004
    Location
    Canada
    Posts
    162
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •