Convert Varchar TO_DATE - HELP
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Convert Varchar TO_DATE - HELP

  1. #1
    Join Date
    Mar 2012
    Location
    Sao Paulo - Brazil
    Posts
    4

    Convert Varchar TO_DATE - HELP

    Hello fellows,

    In my work was installed ORACLE 11gR2 in Linux (RHEL5 64 bits)
    In one table exist one filed VARCHAR2(8) with filled '20110101' (date string)
    I tried execute one SQL Statement to catch an interval in this filed.

    But, occurs message error: Literals in the input must be the same length as literals in the format string (with the exception of leading whitespace). If the
    "FX" modifier has been toggled on, the literal must match exactly,
    with no extra whitespace.

    My SQL:
    SELECT * FROM SE101
    WHERE TO_DATE(E1_VENCTO, 'DD/MM/RR') >= TO_DATE(sysdate, 'DD/MM/RR')

    And my NLS_LANGE (select * from v$nls_parameters) is:
    NLS_DATE_FORMAT DD/MM/RR

    I tryed change my SQL to:
    SELECT * FROM SE101
    WHERE TO_DATE(E1_VENCTO, 'YYYYMMDD') >= TO_DATE(sysdate, 'YYYYMMDD')

    But still dosen't work.
    Anybody can help me pls ?

    Best regards,

    Victor Peres

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Try this.

    Code:
    SELECT * 
      FROM SE101
     WHERE TO_DATE(LPAD(E1_VENCTO, 8, '0'), 'DD/MM/RR') >= sysdate;
    You don't need to convert sysdate to a date, it is a date.
    this space intentionally left blank

  3. #3
    Join Date
    Mar 2012
    Location
    Sao Paulo - Brazil
    Posts
    4
    Hi,

    I tried your command, but still error occour:

    literal does not match format string"
    *Cause: Literals in the input must be the same length as literals in
    the format string (with the exception of leading whitespace). If the
    "FX" modifier has been toggled on, the literal must match exactly,
    with no extra whitespace.
    *Action: Correct the format string to match the literal.

    Many tks.

    Victor Peres

  4. #4
    Join Date
    Mar 2012
    Location
    Sao Paulo - Brazil
    Posts
    4
    So ...

    Freak issue happing now. lol

    When I execute using DD/MM/RR (mask) in SELECT clause =
    Date: 20110512
    Returns: 20/11/12

    When I execute using YYYYMMDD (mask) in SELECT clause =
    Date: 20110512
    Returns: 12/05/11 (right)

    But, I use in WHERE clause:
    SELECT * FROM SE1010
    AND TO_DATE(E1_VENCTO, 'YYYYMMDD') >= sysdate

    This error occurs:
    01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
    *Cause: Illegal year entered
    *Action: Input year in the specified range

    But the year is ok, not exist an invalid year.
    Pls, help me ! I need resolve this issue.

    Many thanks

    Best regards,

    Victor Peres

  5. #5
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,999
    Are you sure that the format is YYYYMMDD and not DDMMYYYY?
    Could the format be reversed for some of the data?

    Try
    SELECT LENGTH(E1_VENCTO), count(*)
    from SE1010
    group by LENGTH(E1_VENCTO)
    order by 1;

    and

    SELECT substr(E1_VENCTO, 1, 4), count(*)
    from SE1010
    group by substr(E1_VENCTO, 1, 4)
    order by 1;

    You might see some data that you did not expect.
    this space intentionally left blank

  6. #6
    Join Date
    Mar 2012
    Location
    Sao Paulo - Brazil
    Posts
    4
    Hi,

    Well, I use your query and my data is fine.
    Check this out:

    E1_VENCTO = '20110512' (original data in E1_VENCTO, varchar field)
    SUBSTR(1, 4) = 2011 (YYYY)
    SUBSTR(5, 2) = 05 - (MM)
    SUBSTR(7, 2) = 12 - (DD)
    COUNT(*) = 5

    I execute this query and works fine:
    SELECT * FROM SE1010
    WHERE E1_VENCTO = '20110512'

    but, when I try this simple query:
    SELECT * FROM SE1010
    WHERE TO_DATE(E1_VENCTO) = TO_DATE('20110512')

    error occurs:
    01861. 00000 - "literal does not match format string"
    *Cause: Literals in the input must be the same length as literals in
    the format string (with the exception of leading whitespace). If the
    "FX" modifier has been toggled on, the literal must match exactly,
    with no extra whitespace.
    *Action: Correct the format string to match the literal.

    And I change the format to YYYYMMDD, DDMMYYYY, DDMMRR, RRRRMMDD, RRMMDD and all show me same error.

    except MMDDYYYY, this format show me "invalid month" errormessage.

    One information, this data was export from SQLSERVER to ORACLE. In SQL Server, the data was VARCHAR in ORACLE VARCHAR but I not sure is the problem.

    This error drive me crazy.


    Many thanks.

    Best regards

    Victor Peres

  7. #7
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,471

    Cool

    Quote Originally Posted by VictorPeres View Post
    ...
    but, when I try this simple query:
    SELECT * FROM SE1010
    WHERE TO_DATE(E1_VENCTO) = TO_DATE('20110512')

    error occurs:
    01861. 00000 - "literal does not match format string"
    ...
    You need to be consistent an supply the correct date format:
    Code:
    SELECT * FROM SE1010
    WHERE TO_DATE(E1_VENCTO,'YYYYMMDD') = TO_DATE('20110512','YYYYMMDD')
    "The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width