AnsweredAssumed Answered

Converting month abbreviations to month numbers

Question asked by EricMurphy on Jun 14, 2013
Latest reply on Jun 15, 2013 by JimMac

Title

Converting month abbreviations to month numbers

Post

     I have a solution that imports a CSV file that probably originated with some huge Oracle database somewhere. For some reason, the date field in the CSV file is idiotically formated as dd/month name abbreviation/yyyy, e.g., 13/Nov/1997.

     I was able to convert this monstrosity to a regular date by decomposing it into separate field for day-of-month, month, and year, and then concatenating them into a single field. The biggest problem, of course, was converting a three-character abbreviated month name into a month number. The way I did it was:

     Case (Status Month Name  = "Jan" ; 1 ; Status Month Name  = "Feb" ; 2 ; Status Month Name  = "Mar" ; 3 ; Status Month Name  = "Apr" ; 4 ; Status Month Name  = "May" ; 5 ; Status Month Name  = "Jun" ; 6 ; Status Month Name  = "Jul" ; 7 ; Status Month Name  = "Aug" ; 8 ; Status Month Name  = "Sep" ; 9 ; Status Month Name  = "Oct" ; 10 ; Status Month Name  = "Nov" ; 11; Status Month Name  = "Dec" ; 12 )

     It ain't pretty, but it works. But is there a better way to do it?

Outcomes