Home » SQL & PL/SQL » SQL & PL/SQL » UTC Date time should be in format 2010-02-24T17:08:09Z. (oracle 10g)
UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445452] Mon, 01 March 2010 23:51 Go to next message
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 #445454 is a reply to message #445452] Mon, 01 March 2010 23:54 Go to previous messageGo to next message
BlackSwan
Messages: 24915
Registered: January 2009
Senior Member
Post Operating System (OS) name & version for DB server system.
Post results of
SELECT * from v$version

there is NO relationship between arbitrary strings and dates
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 Go to previous messageGo to next message
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 #445464 is a reply to message #445452] Tue, 02 March 2010 00:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
now i want UTC Date time should be in format 2010-02-24T17:08:09Z.

Date should be stored in date or timestamp% columns not in string.

How you display it is with the help of TO_CHAR function. Read this page and follow the format link.

Regards
Michel
Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445468 is a reply to message #445464] Tue, 02 March 2010 00:31 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
is there any query on this
Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445470 is a reply to message #445468] Tue, 02 March 2010 00:35 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes there is, read the link I posted and its opposite TO_DATE, this will also validate your date/time.

Or you can simply use REPLACE and concatenation to put the characters you want in the right place.

Regards
Michel
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 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
rajasekhar857 wrote on Tue, 02 March 2010 07:31
is 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

Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445475 is a reply to message #445473] Tue, 02 March 2010 01:15 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
I written this using sysdate but if i need to use my varchar field ABC then how to do.

SELECT TO_char(sysdate,'YYYY-MM-DD')
||'T'
||TO_char(sysdate,'hh:mm:ss')
||'Z'
FROM a
Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445479 is a reply to message #445475] Tue, 02 March 2010 01:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63818
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
input:  02-27-2009 11:01:33
output: 02-27-2009T11:01:33Z

What should be done? 2 ways for example:
1/ Replace space by T and add Z
2/ take the 10 first characters, add a T, put the 8 last characters, add a Z

Regards
Michel
Re: UTC Date time should be in format 2010-02-24T17:08:09Z. [message #445491 is a reply to message #445479] Tue, 02 March 2010 03:09 Go to previous message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
HI GOT IT

Update a set
a.abc = TO_char(To_date(a.abc,'mm-dd-yyyy hh24:mi:ss'),'YYYY-MM-DD') ||'T' ||
TO_char(To_date(a.abc,'mm-dd-yyyy hh24:mi:ss'), 'hh24:mi:ss')||'Z'

[Updated on: Tue, 02 March 2010 03:20]

Report message to a moderator

Previous Topic: a better way than MINUS
Next Topic: Duplicate
Goto Forum:
  


Current Time: Sat Oct 01 09:15:07 CDT 2016

Total time taken to generate the page: 0.13303 seconds