Home » SQL & PL/SQL » SQL & PL/SQL » varchar2 data change 'mmyyyy' to 'yyyymm' (Oracle 10g, Win7)
varchar2 data change 'mmyyyy' to 'yyyymm' [message #574211] Tue, 08 January 2013 00:12 Go to next message
nischalinn
Messages: 118
Registered: May 2012
Location: nepal
Senior 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 #574212 is a reply to message #574211] Tue, 08 January 2013 00:17 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
if you want it in DATE type, use TO_DATE function with format mask 'MMYYYY'.

if you want the output in again VARCHAR type but with different format, use TO_CHAR over above TO_DATE function with format 'YYYYMM'

regards,
Delna

[Updated on: Tue, 08 January 2013 00:20]

Report message to a moderator

Re: varchar2 data change 'mmyyyy' to 'yyyymm' [message #574213 is a reply to message #574211] Tue, 08 January 2013 00:19 Go to previous messageGo to next message
Littlefoot
Messages: 19887
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 #574214 is a reply to message #574213] Tue, 08 January 2013 00:23 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member

i think you are excepting this one...

select to_char(to_date(ADM_DATE,'MMYYYY'),'MMYYYY'),
 to_char(to_date(ADM_DATE,'MMYYYY'),'YYYYMM') from CHECK1
Re: varchar2 data change 'mmyyyy' to 'yyyymm' [message #574217 is a reply to message #574211] Tue, 08 January 2013 01:28 Go to previous message
Michel Cadot
Messages: 59991
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
Previous Topic: CREATING FUNCTION FOR "CASE STATEMENT"
Next Topic: Partitioned table information
Goto Forum:
  


Current Time: Fri Dec 19 04:36:56 CST 2014

Total time taken to generate the page: 0.08631 seconds