so I have a third party database I do an ETL to another third party system. One of the fields in the extract database table is a PeriodID field. unfortunately this third party application uses a "name" i.e. January, February, March etc etc. the system I am loading to needs an EOM date. i.e. 1/31/2019 2/28/2019 3/31/2019 etc. etc.
I did this brute force code that works just fine, but would require annual maintenance. Can anyone help me with a better solution?
When PeriodID = 'January' THEN '1/31/2019'
When PeriodID = 'February' THEN '2/28/2019'
When PeriodID = 'March' THEN '3/31/2019'
When PeriodID = 'April' THEN '4/30/2019'
When PeriodID = 'May' THEN '5/31/2019'
When PeriodID = 'June' THEN '6/30/2019'
When PeriodID = 'July' THEN '7/31/2019'
When PeriodID = 'August' THEN '8/31/2019'
When PeriodID = 'September' THEN '9/30/2019'
When PeriodID = 'October' THEN '10/31/2019'
When PeriodID = 'November' THEN '11/30/2019'
Else '12/31/2019' END