Home » SQL & PL/SQL » SQL & PL/SQL » Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00) (Oracle 11g)
Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00) [message #665799] Wed, 20 September 2017 17:10 Go to next message
manikandan23
Messages: 25
Registered: February 2017
Junior Member
Hi,

I am trying to get the date format with timezone info in Oracle SQL.

The format I want the date to be displayed is given below:

YYYY-MM-DDThh:mm:ss.sTZD (eg: 1997-07-16T19:20:30.45+01:00)

When I use to_char(sysdate,'YYYY-MM-DDThh:mm:ss.sTZD'), it gives me the error saying incorrect format.

Any help would be appreciated.

Thanks,
Mani
Re: Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00) [message #665800 is a reply to message #665799] Wed, 20 September 2017 18:00 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2687
Registered: January 2010
Location: Connecticut, USA
Senior Member
SYSDATE returns DATE and Oracle DATEs do not store time zone information. Use SYSTEMISTAMP instead - it returns TIMESTAMP WITH TIME ZONE. Also T is not a valid format element and should be enclosed in double quotes. Format element for fractional part of a second is ff, so it should be ss.ff, not as ss.s. Format element hh implies 12 hour clock and should be used together with AM or PM - I'll use 24 hour clock. To display himezone in hours and minutes use txh:tzm. And in general, RTFM - all this is clearly documented:

SQL> select  to_char(systimestamp,'YYYY-MM-DD"T"hh24:mi:ss.ffTZH:TZM')
  2    from  dual
  3  /

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD"T"
-----------------------------------
2017-09-20T18:59:25.040000-04:00

SQL> 

SY.

[Updated on: Wed, 20 September 2017 18:12]

Report message to a moderator

Re: Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00) [message #665801 is a reply to message #665799] Wed, 20 September 2017 18:05 Go to previous messageGo to next message
BlackSwan
Messages: 25742
Registered: January 2009
Location: SoCal
Senior Member
manikandan23 wrote on Wed, 20 September 2017 15:10
Hi,

I am trying to get the date format with timezone info in Oracle SQL.

The format I want the date to be displayed is given below:

YYYY-MM-DDThh:mm:ss.sTZD (eg: 1997-07-16T19:20:30.45+01:00)

When I use to_char(sysdate,'YYYY-MM-DDThh:mm:ss.sTZD'), it gives me the error saying incorrect format.

Any help would be appreciated.

Thanks,
Mani
SYSDATE contains NO timezone details

BTW - "mm" is MONTH in digits as in "09"

http://lmgtfy.com/?q=oracle+SYSTIMESTAMP+%20to_char
Re: Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00) [message #665809 is a reply to message #665801] Thu, 21 September 2017 09:16 Go to previous message
manikandan23
Messages: 25
Registered: February 2017
Junior Member
Thank you Sir
Previous Topic: Update Logic
Next Topic: PL/SQl query to build a list of interval days
Goto Forum:
  


Current Time: Tue Dec 12 02:45:14 CST 2017

Total time taken to generate the page: 0.02782 seconds