Post reply

Case When statement When I assign a string, it say Conversion Failed from varcha

  • May 4, 2022 at 9:46 am

    Go to Answer

    #4026482

    Hi Experts,

    I am not able to understand this issue of User Defined Column not able to assign a string on Case statement

    In the below function, I am ensuring the date is not '1900-01-01 00:00:00.000' and doing datediff.

    , CASE
    WHEN Ddate.CancelDate ='1900-01-01 00:00:00.000' AND Ddate.TADate != '1900-01-01 00:00:00.000' AND Ddate.CDate != '1900-01-01 00:00:00.000'
         THEN DATEDIFF(day,Ddate.CDate,Ddate.TADate)+1
     WHEN Ddate.CancelDate ='1900-01-01 00:00:00.000' AND Ddate.TADate = '1900-01-01 00:00:00.000' THEN 999 --'NO Valid Acceptance Date'
     WHEN Ddate.CancelDate ='1900-01-01 00:00:00.000' AND Ddate.CDate = '1900-01-01 00:00:00.000' THEN  999 --'NO Valid Closing Date'
     ELSE DATEDIFF(day, Ddate.CDate, Ddate.CancelDate)+1
    END AS DateDiff

    In the above code, DateDiff is new custom I am creating, when the date is not valid, I wanted to assign 'No Valid Date' but I get this error

     Conversion failed when converting the varchar value 'NO Valid Acceptance Date' to data type int.

    I am not able to understand this as DateDiff is custom column, I have no constraint but if my assumption that DateDiff is the reason, then Is my understanding wrong that DateDiff jurisdiction ends with the function and it cannot decide the type constraints on Custom column ? If the above is right cannot i use multiple data types in single Case statement ? That is confusing me

    2. Can I be assured that invalid date is always '1900-01-01 00:00:00.000', even if this date data is received from any other external system ? If not is there any other way to determine all types of Invalid statements

    Thanks as Always

    • This topic was modified 2 years, 6 months ago by  protocoder.

  • May 4, 2022 at 9:58 am

    Answer

    #4026486

    CASE can only return one datatype so you will either have to cast everything to varchar(13) or use NULL for No Valid Date.

    ps Instead of typing out '1900-01-01 00:00:00.000' everywhare you can just use '1900'.

    • This reply was modified 2 years, 6 months ago by  Ken McKelvey.

  • May 4, 2022 at 3:56 pm

    #4026721

    First of all, you didn't actually post the error you're getting.

    Second, what are the datatypes for all of the columns involved"?  That's actually super important to know here.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • May 4, 2022 at 10:26 pm

    This was removed by the editor as SPAM

  • May 4, 2022 at 10:39 pm

    #4026924

    @KenMckelvey thank you. I was resolving it with random integer and your post have given given better response I am after.

    @jeff Modem the Error is in the smaller code block, when you drag to the right 'NO Valid Acceptance Date' to data type int' and data types I pass are DateTime. Sorry really, I tried to edit and remove but I think late in the night I failed. I will edit that now again.

    Also learned that CASE custom column has the same data type as the elements which it is processing.

     

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

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