| varchar2 data change 'mmyyyy' to 'yyyymm' [message #574211] |
Tue, 08 January 2013 00:12  |
 |
nischalinn
Messages: 80
Registered: May 2012
Location: nepal
|
Member |
|
|
CREATE TABLE CHECK(
ADM_DATE VARCHAR2(10)
)
INSERT ALL
INTO CHECK VALUES ('122012')
INTO CHECK VALUES ('112012')
INTO CHECK VALUES ('102012')
INTO CHECK VALUES ('092012')
INTO CHECK VALUES ('082012')
SELECT * FROM DUAL;
ADM_DATE has the data as in format 'MMYYYY' but I've to make it as 'YYYYMM' while the datatype of ADM_DATE is VARCHAR2.
How can I do it?
|
|
|
|
|
|
| Re: varchar2 data change 'mmyyyy' to 'yyyymm' [message #574213 is a reply to message #574211] |
Tue, 08 January 2013 00:19  |
 |
Littlefoot
Messages: 16952
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
Select 2 substrings: MM and YYYY. Then concatenate them again, but switch their positions. A single UPDATE statement is capable of doing that.
By the way: did you try to create your own test case?
SQL> CREATE TABLE CHECK(
2 ADM_DATE VARCHAR2(10)
3 );
CREATE TABLE CHECK(
*
ERROR at line 1:
ORA-00903: invalid table name
SQL>[Updated on: Tue, 08 January 2013 00:19] Report message to a moderator |
|
|
|
|
|
| Re: varchar2 data change 'mmyyyy' to 'yyyymm' [message #574217 is a reply to message #574211] |
Tue, 08 January 2013 01:28 |
 |
Michel Cadot
Messages: 54155
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:ADM_DATE has the data as in format 'MMYYYY' but I've to make it as 'YYYYMM' while the datatype of ADM_DATE is VARCHAR2.
If it is a date it should be in a DATE datatype, then there is no more problem and, more, you cannot enter an invalid year or month like 'MICHEL' or '999999' or '----------'.
Regards
Michel
|
|
|
|
] 
Members
Search
Help
Register
Login
Home
