Convert Varchar TO_DATE - HELP

[VictorPeres]

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

[gandolf989]

Try this.

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.

[VictorPeres]

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

[VictorPeres]

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

[gandolf989]

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.

[VictorPeres]

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

[LKBrwn_DBA]

...
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:

SELECT * FROM SE1010
WHERE TO_DATE(E1_VENCTO,'YYYYMMDD') = TO_DATE('20110512','YYYYMMDD')