Date time string to timestamp with time zone - military zone names
I am writing an ETL process where we receive from an external system date time values in a format like 'YYYYMMDDTHH24MISSZ' with a literal "T" as separator between date and time parts and a trailing letter indicating the time zone as a military time zone (Z=ZULU=UTC, A=ALPHA=UTC+1)
Ignoring the timezone part I have no problem converting the text to a date or timestamp data type (sometimes we need to process fractions of a second).
I now need to store the data with the time zone - is there a neat trick I can do to use these zones or do I have to resort to a case statement on the last character of the incoming string to provide an time offset or even do a look-up.
Oracle 11gR2 - SQL