Post reply

Datatype conversion error_ replace with NULL

  • March 30, 2022 at 9:42 am

    #4008108

    First of all, are you aware of issues with numeric precision when using FLOAT? If not, I suggest you reconsider – DECIMAL() is often a better choice.

    If you put the following code just before your ALTER statement, all should be well.

    UPDATE #Data
    SET Number = NULL
    WHERE TRY_CAST(Number AS FLOAT) IS NULL;

  • March 30, 2022 at 9:47 am

    #4008111

    You can use TRY_CAST or TRY_CONVERT

    UPDATE #Data
    SET Number = TRY_CAST(Number AS float);

    How to post data/code on a forum to get the best help.

  • March 30, 2022 at 10:38 am

    #4008144

    thank you! that did the trick.

  • March 30, 2022 at 4:15 pm

    #4008309

    What reason are you using a float?

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • March 30, 2022 at 9:12 pm

    #4008470

    When I try to run this query on my data (not the sample posted here) I get an invalid object error "Column name" even though the column exists in the model

    Some of the values in the raw dataset are #DIV/0!  or negative. Could that be causing the issue ?

     

    EDIT---

    Please ignore this. Manged to fix it. As with most  SQL issues the cause was amateur SQL user ignorance 😀

Viewing 5 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply