Date Format

[aph]

Hi All,
I have a column VARCHAR2(4), in which they have stored date YYMM like 0512 and I want to convert it into a proper date as format Month- YYYY December - 2005. How I can achive this?

Thanks in advance.

[LKBrwn_DBA]

Try this:

TO_CHAR(TO_DATE(TheCol||'01','RRMMDD'),'Month - YYYY')

[WilliamR]

Best you can do is

TO_DATE(oldcolumn, 'RRMM')

although whether the result is always correct will depend on your application.

WITH testdata AS
     ( SELECT COLUMN_VALUE AS testcolumn
       FROM   TABLE(SYS.DBMS_DEBUG_VC2COLL('0001', '2202','4403','6604','8805','9912')) )
SELECT t.testcolumn
     , TO_DATE(t.testcolumn,'RRMM')
FROM   testdata t;

TESTCOLUMN TO_DATE(T.TESTCOLUMN,'RRMM')
---------- ----------------------------
0001       01/01/2000
2202       01/02/2022
4403       01/03/2044
6604       01/04/1966
8805       01/05/1988
9912       01/12/1999

6 rows selected

Then you can use TO_CHAR(), NLS_DATE_FORMAT or your application settings to format the results how you want.

[aph]

Thanks,

Can I extract the data using the format?
e.g need data between '0510' and '0512'

Thanks

[aph]

SELECT TO_CHAR(TO_DATE(accounting_date||'01','RRMMDD'),'Month - YYYY') from TABLE_A
WHERE TO_CHAR(TO_DATE(accounting_date||'01','RRMMDD'),'Month - YYYY') BETWEEN 'October - 2005' AND 'November - 2005'

No row return.

[WilliamR]

Perhaps you meant to check for a date value between two other date values, not a character string between two other character strings.

e.g.
WHERE TO_DATE(accounting_date,'RRMM') BETWEEN DATE '2005-10-01' AND DATE '2005-11-01'

I'm still not convinced you need the

|| '01'

but you seem to like it

[aph]

Accounting_date Is Varchar2(4) Type.