CODEWITHSUNDEEP
sqloracle-database
user1200687
user1200687

Reputation: 41

update date value in oracle

I need to convert date format in 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 don't really know SQL but did some research. Here is the command that I consultanted other people on the forum. but it throws me unrecognized command error. table name is B and column name is First

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

Could anyone here help me with it? thanks in advance.

Upvotes: 2

Views: 5536

Answers (2)

Ben
Ben

Reputation: 52853

Umm... I'm either missing something extremely obvious or everyone else is.

You want to date operations? Use to_date and to_char. I'm going to assume this ss:sss means, seconds, then fractional seconds. You date appears to be 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' )

Generally, when using dates it's far, far easier to only use date functions and the provided formats.

As an added point if you have a date, store it as a date. It'll save a world of bother later on.

Upvotes: 0

Michael Berkowski
Michael Berkowski

Reputation: 270599

The "unrecognized command" is merely a misspelling of UPDATE:

UPDATAE B

// Should be
UPDATE B

To verify the result is what you expect before executing the UPDATE statement, use a SELECT:

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

Upvotes: 1

Related Questions