Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Manikandan.

Asked: September 20, 2017 - 4:11 pm UTC

Last updated: September 22, 2017 - 12:32 am UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Hi,

I am trying to get the date with time zone in the below format (ISO 8601 I GUESS)

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


where:
YYYY = four-digit year
MM = two-digit month (01=January, etc.)
DD = two-digit day of month (01 through 31)
hh = two digits of hour (00 through 23) (am/pm NOT allowed)
mm = two digits of minute (00 through 59)
ss = two digits of second (00 through 59)
s = one or more digits representing a decimal fraction of a second
TZD = time zone designator (Z or +hh:mm or -hh:mm)

Any help would really be appreciated.

Thank you

and Connor said...

sysdate is the current date, and current time only.

systimestamp is what you want.

SQL> select sysdate from dual;

SYSDATE
---------
21-SEP-17

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------
21-SEP-17 08.42.33.222000 AM +08:00


You have total control over the output format 

SQL> select to_char(systimestamp,'YYYY-MM-DD hh24:mi:ss.ff TZR') from dual;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DDHH24:MI:SS.FFTZR')
--------------------------------------------------------------
2017-09-21 08:46:24.752000 +08:00


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Excellent and Thank you so much!

Manikandan Arunachalam, September 21, 2017 - 2:48 am UTC

Hi,

I really wanted this one only and it helped.

Thanks and Appreciate it.
Connor McDonald
September 21, 2017 - 2:53 am UTC

glad we could help

"Date only" ..?

NextName, September 21, 2017 - 12:16 pm UTC

Isn't it a bit misleading to say "sysdate is the current date, and current date only."?

SQL> select to_char(sysdate, 'yyyy-mm-dd hh24:mi') as myDateAndTime from dual;

MYDATEANDTIME
----------------
2017-09-21 14:13

SQL>



Connor McDonald
September 22, 2017 - 12:32 am UTC

Sorry - typo on my part.

I've corrected the answer.

Not misleading at all really

paul, September 21, 2017 - 2:42 pm UTC

If you look at the Oracle SQL Reference

https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#SQLRF0021

[..] Valid date range from January 1, 4712 BC, to December 31, 9999 AD. The default format is determined explicitly by the NLS_DATE_FORMAT parameter or implicitly by the NLS_TERRITORY parameter. The size is fixed at 7 bytes. This data type contains the datetime fields YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND. It does not have fractional seconds or a time zone. [..]

Maybe Connor should have just capitalized "date" ;)

Connor McDonald
September 22, 2017 - 12:31 am UTC

No - sorry, that was my bad typo.

"sysdate is the current date, and current date only. "

should have been:

"sysdate is the current date, and current time only."

I've updated the answer
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library