1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

TO_DATE conversion of a column which contains dates in different NLS_DATE_LANGUAGEs

Discussion in 'SQL PL/SQL' started by albot, May 4, 2013.

  1. albot

    albot Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi all,

    I'm not very experienced with smart SQL queries so I wanted to ask some feedback on potential options for the issue I have.

    I have encountered a rather stupid problem when I wanted to generate an ad-hoc query (see below)

    select s.collection_key ckey,
    pv.enteredvalue, to_date(substr(pv.enteredvalue,1,11),'dd-MON-yyyy', 'NLS_DATE_LANGUAGE = American')
    from
    en_sections s,
    en_fields f,
    en_properties p,
    en_property_values pv
    where f.name = 'General Information'
    and p.field_key = f.primary_key
    and pv.field_key = f.primary_key
    and pv.property_key = p.primary_key
    and pv.section_key = s.primary_key
    and pv.version_deleted is null
    and pv.enteredvalue is not null

    Unfortunately, the column pv.enteredvalue seems to contain American date formats and Dutch date formats (e.g. 12-Jan-2013, 10-mei-2010, the latter date should be 12-May-2010) and we are not allowed to change the entered values in this column (audited lab notebook system).

    Is there anyone who has a brilliant idea how to solve this problem?
    Any help is very much appreciated

    Best regards, Alex
     
  2. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Re: TO_DATE conversion of a column which contains dates in different NLS_DATE_LANGUAG

    Hi Alex,

    Is there any other column to identify the language of the date ?? If there , by using decode() or case statement you can try to solve this..

    Regards
    Sambasiva Reddy.K
     
  3. albot

    albot Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Re: TO_DATE conversion of a column which contains dates in different NLS_DATE_LANGUAG

    Thanks very much, Sambasiva.

    With your hint I carefully looked at all available columns again and luckily, there is one proper data column based on the enteredvalue column! Did not see this one before...

    Regards,
    Alex
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Re: TO_DATE conversion of a column which contains dates in different NLS_DATE_LANGUAG

    Hi Alex,

    If you have a language field or any field contains language, then you can use case or decode as suggested by sambasiva.