Search code examples
sqlssms-2012

Dynamic date case statement

I'm using SQL Management Studio 2012.I Have a query output like below. I would like to have a column that looks at the date completed and if it's greater that 2019-01-01 then the exam is'valid' if earlier then 'Renew'. It can't be a hardcoded 2019-01-01, at the turn of the year I would like it to be looking at 2020-01-01 and so on.

Name         Exam         Date Completed  
Matt         English        2018-11-26       
James        Maths          2019-06-02

I would like

Name         Exam         Date Completed        Valid
Matt         English        2018-11-26            No
James        Maths          2019-06-02            Yes
Solution

  • I think you simply want to know if the year is the same as the current year. That would be:

    (case when year(date_completed) = year(getdate()) then 'Yes' else 'No'
 end) as valid