CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
user1200687
user1200687

Reputation: 41

update date format in SQL developer

I need to convert date format in SQL 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 and found that I can use instr to find the string and replace it, however, no matter what I try,there is always something off :( Could anyone here help me with it? thanks in advance.

By the way, it's in oracle sql developer so syntax are different from previous answer. Thanks again

Upvotes: 1

Views: 4018

Answers (6)

user1200687
user1200687

Reputation: 41

thank you so much for your guys help! I got this sorted out by other user's help, the command is,

update b
set first= to_char(substr(FIRST,1,4)||'-'||substr(FIRST, 6, 2)||'-'||substr(FIRST, 9, 2)||' '||substr(FIRST, 12, 8))

Thanks :) Mylie

Upvotes: 1

John Dewey
John Dewey

Reputation: 7093

declare @ds varchar(23)='2012/02/16 12:57:03:002'; --your current format
select convert(varchar(30),cast(@ds as datetime),120)+' CST' --new format you specified

Upvotes: 0

Taryn
Taryn

Reputation: 247650

If your current column is stored as a varchar (which it looks like it is based on your example) you can convert it the following way to a datetime.

declare @date varchar(25)
set @date = '2012/02/16-09:40:30:000'

select Convert(datetime, Left(@date, 4) + '-' + 
        substring(@date, 6, 2) + '-' + 
        substring(@date, 9, 2) + ' ' + 
        substring(@date, 12, 8)) As NewDate

And the result would be 2012-02-16 09:40:30.000

There are lots of sites that have details on converting datetime:

  1. http://www.sql-server-helper.com/tips/date-formats.aspx
  2. http://anubhavg.wordpress.com/2009/06/11/how-to-format-datetime-date-in-sql-server-2005/

If the date is stored as a datetime, then you can get your format by doing:

SELECT convert(varchar, getdate(), 120)

Upvotes: 1

Lamak
Lamak

Reputation: 70638

If the date is stored as a DATETIME, then you can convert it to a string in the format you want by doing the following:

SELECT CONVERT(VARCHAR(19),YourColumn,120)
FROM YourTable

CAST AND CONVERT

Upvotes: 0

Phil
Phil

Reputation: 497

http://msdn.microsoft.com/en-us/library/ms187928.aspx

If you're using SQL Server and it's a date field you can do:

convert(varchar,getdate(),120)

Replacing getdate() with the column name

Upvotes: 0

njr101
njr101

Reputation: 9619

The CONVERT() function is normally used for this. The third parameter lets you specify the required date formatting.

If the date is already in a string, then convert it first into a date type and then convert it back to a string in the required format.

Upvotes: 0

Related Questions