Post reply

How to get 'Year' in a case statement

  • November 19, 2015 at 8:55 am

    #302432

    I am using the case statement, below, in my query. What the it's doing is pulling the answer from a question in an assessment. If the answer is Yes then give me the full date of birth....if No...just give me the year. It works perfect until I try to get the year.

    ,max(case when Assessments_View_Derived.field_name = '*Was patient-signed manufacturer HIPAA consent received?' then (case when Assessments_View_Derived.answer = 'yes' then CONVERT(VARCHAR(10),hr.dob, 101) else year(hr.dob) end)end) DOBs

    I get this error message when I add the year portion:

    Msg 245, Level 16, State 1, Line 2

    Conversion failed when converting the varchar value '02/04/1930' to data type int.

    Warning: Null value is eliminated by an aggregate or other SET operation.

    I kind of get what the error is saying...but, I'm not sure how to fix it.

  • November 19, 2015 at 9:02 am

    #1841038

    Quick suggestion, convert the year to varchar

    😎

  • November 19, 2015 at 9:03 am

    #1841040

    could be a couple of things, but firstly YEAR(hr.dob) will return an integer so you need to convert it to a VARCHAR so try

    CONVERT(VARCHAR(10),year(hr.dob))

    That should help fix it.

    _________________________________________________________________________
    SSC Guide to Posting and Best Practices

  • November 19, 2015 at 9:06 am

    #1841042

    Thanks! For the help. I was thinking that I had to put the convert code at the end (ex. Convert(Varchar....101)...so that kept me from doing it. LOL

  • November 19, 2015 at 11:05 am

    #1841104

    cory.bullard76 (11/19/2015)

    Thanks! For the help. I was thinking that I had to put the convert code at the end (ex. Convert(Varchar....101)...so that kept me from doing it. LOL

    Just use DATENAME(yy,columnname). No need for a CONVERT or CAST that way.

    --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)

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

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