none
Getting error: Conversion failed when converting the nvarchar value '0 AM' to data type int.

    Question

  • FROM

             dbo.Columns


    WHERE    

    (LOANSTATUS = N'Closed') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'closedbrokered') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Suspended') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Processing') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Loan Originated') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Funded') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Forward Lock') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Docs Out') AND(RIGHT(StatusDate, 4) =Year(getdate())) OR


                         

    (LOANSTATUS = 'Docs Back') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Denied') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Cancelled') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Approved Outside') AND(RIGHT(StatusDate, 4) = Year(getdate())) OR


                         

    (LOANSTATUS = 'Approved LHF') AND(RIGHT(StatusDate, 4) = Year(getdate()))

    Monday, November 18, 2013 11:21 PM

Answers

  • First, your RIGHT(StatusDate,4) expression is getting converted to an INT based on the return data type of the YEAR() function.  For more, read the data type precedence article: http://technet.microsoft.com/en-us/library/ms190309.aspx

    That said, I would guess that you have data in the StatusDate that looks like "0 AM".  It could be that you are expecting to have those values filtered out by your LOANSTATUS checks, or you have bad data within your expected data set.  Looking at your query, it looks like you are missing parenthesis around each OR block - in other words, add parenthesis around "(LOANSTATUS = 'aaa') AND (RIGHT(...) = YEAR(...))" so that entire branch is executed as an OR with the next. (replacing my double-quotes with a parenthesis).


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    Tuesday, November 19, 2013 12:24 AM
    Moderator

All replies

  • From the error, I guess that you may have to use LEFT function instead of RIGHT ?


    Narsimha

    Monday, November 18, 2013 11:31 PM
  • please post all query. are you using any convert function in it?


    ESHANI. Please click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if a post has been useful to you

    Monday, November 18, 2013 11:33 PM
  • First, your RIGHT(StatusDate,4) expression is getting converted to an INT based on the return data type of the YEAR() function.  For more, read the data type precedence article: http://technet.microsoft.com/en-us/library/ms190309.aspx

    That said, I would guess that you have data in the StatusDate that looks like "0 AM".  It could be that you are expecting to have those values filtered out by your LOANSTATUS checks, or you have bad data within your expected data set.  Looking at your query, it looks like you are missing parenthesis around each OR block - in other words, add parenthesis around "(LOANSTATUS = 'aaa') AND (RIGHT(...) = YEAR(...))" so that entire branch is executed as an OR with the next. (replacing my double-quotes with a parenthesis).


    Phil Brammer | http://www.ssistalk.com | Twitter: http://twitter.com/PhilBrammer

    Tuesday, November 19, 2013 12:24 AM
    Moderator
  • Hello Rocket,

    Remove the RIGHT function,

    (LOANSTATUS = 'Approved LHF') AND(RIGHT(StatusDate, 4) = Year(getdate()))

    as

    (LOANSTATUS = 'Approved LHF') AND (Year(StatusDate) = Year(getdate()))

    Note: I am assuming that StatusDate datatype is datetime. If it is a string then we need to convert it into datetime first and then apply Year function.


    Regards, RSingh

    Tuesday, November 19, 2013 5:54 AM
  • Rocket ST,

    I am assuming that the given filters are only filters in your statement. If yes, then you should write the statement as below:

    LOANSTATUS IN ('Closed',
                    'ClosedBrokered',
                    'Suspended',
                    'Processing',
                    'Loan Originated',
                    <and so on>) 
            AND YEAR(StatusDate)=YEAR(GETDATE())
    
    

     


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 19, 2013 7:30 AM