• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1875
  • Last Modified:

to_date between oracle and teradata using oracle gateway

this column "Date_Pay" is CHAR(10) at teradata WH and it looks like this
'2010-10-18'


when I try this sql filter
AND cast(cl."Date_Pay" as date) BETWEEN TO_DATE ('2010/01/01',
                                                       'yyyy/mm/dd')
                                          AND  TO_DATE ('2010/01/02',
                                                        'yyyy/mm/dd')

I get error msg ORA-01861

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.
0
it-rex
Asked:
it-rex
  • 2
  • 2
1 Solution
 
AkenathonCommented:
Instead of CAST, try using TO_DATE on the field.

Try this: AND cast(cl."Date_Pay" as date) < sysdate

...and you'll see the to_dates you already have are not the cause of the problem.
0
 
it-rexAuthor Commented:
how can I use a between clasue,here?
0
 
AkenathonCommented:
You CAN use the between. That part is not wrong. The part which is wrong is the portion before the between

You need to convert your character field to a date. That is not done using CAST, it is done using to_date in this fashion:


AND TO_DATE(cl."Date_Pay", 'yyyy/mm/dd') BETWEEN TO_DATE ('2010/01/01',
                                                       'yyyy/mm/dd')
                                          AND  TO_DATE ('2010/01/02',
                                                        'yyyy/mm/dd')
0
 
it-rexAuthor Commented:
thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now