In oracle, what happens when a value in date time format is inserted into a field in varchar2 format ?, Oraclevarchar2
-- Create Table test1
Create table TEST1
(
ID VARCHAR2 (40) default sys_guid (),
TDATE VARCHAR2 (200)
)
Tablespace APP_TX_DATA
Pctfree 10
Pctused 40
Initrans 1
Maxtrans 255
Storage
(
Initial 64 K
Minextents 1
Maxextents unlimited
);
-- Insert date time format data
Insert into test1 values (sys_guid (), sysdate + 10 );,
-- Query data
Select * from test1 -- the value of tdate is from month 7 to month 15.
-- Q: How do I convert the existing data format from to the normal format of YYYYMMDD?
Update test1 t set t. tdate = '20' | substr (t. tdate, instr (t. tdate, '-', 1, 2) + 1, 2)
| Case instr (t. tdate, 'month', 1, 1)-(instr (t. tdate, '-', 1, 1) + 1)
When 1 then '0' | substr (t. tdate,
Instr (t. tdate, '-', 1, 1) + 1,
(Instr (t. tdate, 'month', 1, 1)-(instr (t. tdate, '-', 1, 1) + 1 ))
When 2 then substr (t. tdate,
Instr (t. tdate, '-', 1, 1) + 1,
(Instr (t. tdate, 'month', 1, 1)-(instr (t. tdate, '-', 1, 1) + 1 ))
Else t. tdate end
| Substr (t. tdate, 0, 2)
Where t. tdate like '% month %' order by t. tdate
Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.