1

I want to convert varchar2(24-JAN-16) to number(20160124) in Oracle and Datastage.

Can you help me?

Thanks in advance.

Wiz
  • 113
  • 12
  • 2
    Why? Why not convert it to date? – BobC Mar 10 '17 at 19:01
  • Let me be a bit more assertive. You really **should** convert it to DATE. – BobC Mar 10 '17 at 19:01
  • 1
    To be clear, I'm with @BobC 100%. But if you really have a legitimate use case for this, then something like this could help. `SELECT TO_NUMBER(TO_CHAR(TO_DATE('24-JAN-16','DD-MON-RR'), 'YYYYMMDD')) FROM dual;` – Chris Hep Mar 10 '17 at 19:13
  • And if you do have a legitimate used case, please share it :) – BobC Mar 10 '17 at 19:14
  • 1
    @HepC you should post your comment as an answer. – Bohemian Mar 10 '17 at 19:17
  • @Bohemian I always hesitate posting an answer that I hope isn't the final solution. Even though it answers the exact question, I'm hoping the solution is that a date is acceptable. – Chris Hep Mar 10 '17 at 19:40
  • And incidently, the subject and the question are subtly different :) – BobC Mar 10 '17 at 22:20

1 Answers1

2

If this is a firm requirement - and you can't just use this as a date - then the following SQL will accomplish taking a string, and converting it the the number you want.

SELECT TO_NUMBER(TO_CHAR(TO_DATE('24-JAN-16','DD-MON-RR'), 'YYYYMMDD')) FROM dual;
Chris Hep
  • 1,121
  • 8
  • 13
  • Thank you all your replies. I have to convert to number, because the DW has all field with day_code as a number (pe, 20170313). I know this situation is not desirable, but I am just following my company's requirement. – Wiz Mar 13 '17 at 09:50