Want to update the table1 time field time1 date, find out the time field from table2 time2 insert time1, existing problems, time2 partial data for "10 August 16," this format, part of the "2015-08-16" as the standard format to all time2 format into a standard "2015-08-16", not directly use the to_date and to_char, decided to string concatenation. So you need to judge what is the original format respectively, and then, using a case, when the first time2 in standard format,

Select
(case
The when length (time2)=10 & amp; & Substr (time2, 0, 1)!
='2'Then to_date ((to_char (sysdate, 'yyyy) | |' - '| | substr (time2, 0, 2) | |' - '| | substr (time2, 3, 2)),' - dd yyyy - MM)
The else 'not time'
End)
The from table2 d


Now an error, ora - 00905: missing keywords, masters who help you solve it??????? Thanked him little women

CodePudding user response:

The & amp; & Replace the AND

CodePudding user response:

& & How so familiar in the shell,,

CodePudding user response:

Conditions just a can, at the same time after the else should be a to_date (time2, 'yyyy - mm - dd)

 
Select 
(case 
The when substr (time2, 0, 1)! 
='2'Then to_date ((to_char (sysdate, 'yyyy) | |' - '| | substr (time2, 0, 2) | |' - '| | substr (time2, 3, 2)),' - dd yyyy - MM) 
The else to_date (time2, 'yyyy - mm - dd) 
End) 
The from table2 d 

CodePudding user response:

 
Select 
(case 
The when length (sysdate)=10 and substr (sysdate, 0, 1)! 
='2'Then to_date ((to_char (sysdate, 'yyyy) | |' - '| | substr (sysdate, 0, 2) | |' - '| | substr (sysdate, 3, 2)),' - dd yyyy - MM) 
The else sysdate 
End) 
The from dual d 

Should be, then or else the result type should be consistent, not one is a date, is a character,

CodePudding user response:

The create table table1 (a date);
The create table table2 (b varchar2 (100));

Insert into table2 (b) values (' August 16 at 10 ');
Insert into table2 (b) values (' 2015-08-16 ');
COMMIT;


INSERT INTO table1 (a)
SELECT to_date (CASE SUBSTR (b, 3, 1) the WHEN 'month' THEN '2015 -' | | SUBSTR (b, 1, 2) | | '-' | | SUBSTR (b, 4, 2) the ELSE b END, '- dd yyyy - mm) AS col1 FROM table2.
COMMIT;

CodePudding user response: