timezone conversion

Vicky007

Hi ,

Any idea how we can parse this format of timezone : 2018-04-16T16:55:52+0530

I tried multiple options but no luck. I am looking for timestamp format expression which can be used in SQLLDR. i tried 'YYYY-MM-DD"T"HH24MISS'  and various combination but no luck...

Thanks

odie_63

This should be fine :

my_field  TIMESTAMP WITH TIME ZONE  "YYYY-MM-DD\"T\"HH24:MI:SSTZHTZM"

If you're not sure about the format to apply, you can always look it up in the documentation :

https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/Format-Models.html#GUID-49B32A81-0904-433E-B7FE-51606672183A

Vicky007

If file column has different formats, is there any possibility to consume data by doing some kind of if else across multiple formats ? like

if format1 is not working then check with format2 and so on ?and if nothing works out then reject.

can anyone help me with sample for such cases ?

odie_63

What's the data type of the target column?

Could you post some of the formats you expect?

Vicky007

Date of birth column datetype is TIMESTAMP(0)

we are getting below formats in file :

• TIMESTAMP WITH TIME ZONE  "YYYY-MM-DD\"T\"HH24:MI:SSTZHTZM"  ---- Major records have these format 95%
• 05/07/1981 ( MM/DD/YYYY ) ----around 3% is in this format
• 19790902000000 ( YYYYMMDDHH23MISS) ------ 1% in this format
• many other

In MYSQL - if format is adhere then it set value to NULL and proceed, there any flag at column level which we can use in this case ? ( as this is not much used column and we dont want to avoid record due to inconsistent data in this column )

cormaco

In MYSQL - if format is adhere then it set value to NULL and proceed, there any flag at column level which we can use in this case ? ( as this is not much used column and we dont want to avoid record due to inconsistent data in this column ) 

Since Oracle 12.2 you can add a default value on conversion error clause to TO_TIMESTAMP_TZ:

7.251 TO_TIMESTAMP_TZ 

odie_63

Vicky007 wrote:Date of birth column datetype is TIMESTAMP(0)

Why not a DATE then?

Anyway, an option would be to create a dedicated conversion function to "try" the different formats.

Here's an example :

create or replace function to_timestamp_multi (  p_value  in varchar2)return timestampdeterministicis  fmtList sys.odcivarchar2list := sys.odcivarchar2list('YYYY-MM-DD"T"HH24:MI:SSTZHTZM','MM/DD/YYYY','YYYYMMDDHH24MISS');begin  for i in 1 .. fmtList.count loop    begin      return to_timestamp_tz(p_value, fmtList(i));    exception      when others then        null;    end;  end loop;  raise_application_error(-20000, utl_lms.format_message('Literal ''%s'' does not match any of the available formats', p_value));end;/

The list of formats could be extended, or parameterized, or cached (if you put the function in a package).

The function raises an exception if the input string matches none of the specified formats, but you could very well return a NULL instead.

There's still room for improvements, for instance regarding timezone handling (which, along with the time portion, is unusual for DOB data).

Usage in control file :

my_field  char(30) "TO_TIMESTAMP_MULTI(:MY_FIELD)"