Oracle update date value

Question

I need to convert date format to ORACLE SQL Developer

The current format is yyyy / mm / dd-hh: mm: ss: sss, and I need to convert it to yyyy-mm-dd hh: mm: ss CST I really don't know SQL, but have done some research. Here is a team that I have advised other people on the forum. but it throws me an unrecognized team error. table name B and column name First

UPDATAE B set First = concat(to_char(substring(FIRST,1,4) + '-' + substring(FIRST, 6, 2) + '-' + substring(FIRST, 9, 2) + ' ' + substring(FIRST, 12, 8)); 

Can anyone here help me? thanks in advance.

Answer 1

An “unrecognized command” is just a typo of UPDATE :

 UPDATAE B // Should be UPDATE B 

To check the result, you expect to use SELECT before executing the UPDATE SELECT :

 SELECT to_char(substr(FIRST,1,4) || '-' || substr(FIRST, 6, 2) || '-' || substr(FIRST, 9, 2) || ' ' || substr(FIRST, 12, 8)) AS Test FROM B 

Answer 2

Umm ... I either miss something extremely obvious, or everyone else.

Do you want to spend a date? Use to_date and to_char . I am going to assume that it is ss:sss means seconds, then fractional seconds. The date is apparently a string, so we need to convert it twice:

 update b set first = to_char( to_date( my_date, 'yyyy/mm/dd-hh:mi:ss:ff3') ,'yyyy-mm-dd hh:mi:ss' ) 

As a rule, when using dates it is much easier to use date functions and the provided formats .

As an added point, if you have a date, save it as a date. In the future, it will save the world.