OraFAQ Forum: SQL & PL/SQL » Need Date format in YYYY-MM-DDThh:mm:ss.sTZD (eg 1997-07-16T19:20:30.45+01:00)

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)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

manikandan23
Messages: 34
Registered: February 2017

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

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 #665800 is a reply to message #665799]

Wed, 20 September 2017 18:00

Solomon Yakobson
Messages: 3305
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

BlackSwan
Messages: 26766
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

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 #665809 is a reply to message #665801]

Thu, 21 September 2017 09:16

manikandan23
Messages: 34
Registered: February 2017

Member

Thank you Sir

Report message to a moderator

Previous Topic:	Update Logic
Next Topic:	PL/SQl query to build a list of interval days

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri May 16 08:38:14 CDT 2025