On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,532 Users
  • 2,269,755 Discussions
  • 7,916,774 Comments

Discussions

timezone conversion

Vicky007
Vicky007 Member Posts: 60
edited May 29, 2020 7:33AM in SQL & PL/SQL

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

Tagged:
Vicky007

Answers

  • odie_63
    odie_63 FranceMember Posts: 8,493 Silver Trophy
    edited May 29, 2020 5:39AM

    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
  • Vicky007
    Vicky007 Member Posts: 60
    edited May 29, 2020 6:04AM

    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
    odie_63 FranceMember Posts: 8,493 Silver Trophy
    edited May 29, 2020 6:29AM

    What's the data type of the target column?

    Could you post some of the formats you expect?

    Vicky007
  • Vicky007
    Vicky007 Member Posts: 60
    edited May 29, 2020 6:39AM

    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
    cormaco Member Posts: 2,071 Silver Crown
    edited May 29, 2020 7:23AM
    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
    odie_63 FranceMember Posts: 8,493 Silver Trophy
    edited May 29, 2020 7:33AM
    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)"
    Vicky007