UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445452] |
Mon, 01 March 2010 23:51  |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
hi,
iam having a table A where column ABC is a varchar field with varchar2(50).
currently data stored is like this.
02-27-2009 11:01:33
02-27-2009 11:01:46
03-06-2009 09:07:18
now i want UTC Date time should be in format 2010-02-24T17:08:09Z.
in the above format.please help me in providing a query related ti this.
|
|
|
|
Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445457 is a reply to message #445454] |
Tue, 02 March 2010 00:01   |
rajasekhar857
Messages: 500 Registered: December 2008
|
Senior Member |
|
|
Microsoft Windows server 2003 Standard edition service pack 2
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL>
|
|
|
|
|
|
Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445473 is a reply to message #445468] |
Tue, 02 March 2010 01:05   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
rajasekhar857 wrote on Tue, 02 March 2010 07:31is there any query on this
If you insist on storing DATE/TIMESTAMP value in VARCHAR2 column, just use SUBSTR function (more than once) to cut all date/time parts and then concatenate them in required order, something like UPDATE a
SET abc = SUBSTR( abc, ... )||'-'||SUBSTR( abc, ... )||...;
Anyway (as there may be non-valid DATE/TIMESTAMP representation in VARCHAR2 column), be prepared for complications, e.g. when trying to re-format already re-formatted value. You may add some checks into WHERE clause (e.g. on dash/pit-pair/number positions), but... would it not be easier to store DATE/TIMESTAMP value in column with DATE/TIMESTAMP type?
[Edit: grammar]
[Updated on: Tue, 02 March 2010 01:06] Report message to a moderator
|
|
|
|
|
|