1

For suppose if I want sysdate,

SELECT SYSDATE as system_date FROM DUAL;

should output in the following format

14-Feb-2018 T19:50:02+00:00

i.e.,

DD-MMM-YYYY Thh:mm:ss+HH:MM
Sai Nikhil
  • 1,237
  • 2
  • 15
  • 39
  • 1
    That is just fine. It is the default. – Gordon Linoff Feb 14 '18 at 19:58
  • Sorry, I might be unclear. I want the output to come in the specified format. How to achieve it? – Sai Nikhil Feb 14 '18 at 20:02
  • What format you want? – Juan Carlos Oropeza Feb 14 '18 at 20:03
  • 1
    There is no `MMM` format model element in Oracle; to get Feb, you should use the element `Mon`. Then, do not use `mm` or `MM` for minutes, `mm` or `MM` means month. `mi` or `MI` is for minutes. Then `hh` is incomplete - it should be either `hh24` for 24-hour clock or `hh ... AM` for AM/PM notation; you probably want `hh24`. –  Feb 14 '18 at 20:30

2 Answers2

2

Assuming you know the date represents UTC and want the +00:00 part to be fixed:

select to_char(sysdate, 'DD-Mon-YYYY "T"HH24:MI:SS"+00:00"') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYY"T"HH24:
------------------------------------
14-Feb-2018 T20:13:08+00:00

The format model elements are in the documentation. That includes a section on character literals, which I've used for the fixed T and +00:00 parts.

As @mathguy said, this seems a bit unusual; and you might actually to leave the column as a native date and have your application or reporting tool or whatever format it for you. It depends what exactly you're doing, and whether you actually want a string value directly from the query.

As your updated question now doesn't have that pseudo-timezone, it's now even simpler, but the same idea:

select to_char(sysdate, 'DD-Mon-YYYY "T"HH24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-MON-YYYY"T
------------------------------
14-Feb-2018 T20:17:50

If you're working with a data type that knows about time zones - i.e. not a plain DATE or TIMESTAMP - you can include those in the formatting using the appropriate model elements:

select to_char(systimestamp, 'DD-Mon-YYYY "T"HH24:MI:SSTZH:TZM') from dual;

TO_CHAR(SYSTIMESTAMP,'DD-MON-YYYY"T"
------------------------------------
14-Feb-2018 T20:24:58+00:00

which happens to still show +00:00 because my system is in the UK. With a different value it shows something appropriate:

alter session set time_zone = 'AMERICA/NEW_YORK';
select to_char(current_timestamp, 'DD-Mon-YYYY "T"HH24:MI:SSTZH:TZM') from dual;

TO_CHAR(CURRENT_TIMESTAMP,'DD-MON-YY
------------------------------------
14-Feb-2018 T15:28:57-05:00

Notice now I'm using systimestamp and current_timestamp, which are TZ-aware, and not sysdate or current_date which are not - you'l get an error if you try to get the TZH or TZM elements from those.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Hi. Thank you so much for your time. I posted the final format. Can you please check again – Sai Nikhil Feb 14 '18 at 20:20
  • @saint1729 - you've now changed the fixed +00:00 to +HH:MM, which implies a variable time zone. But a `DATE` doesn't have a time zone component. So what data type are you really working with? – Alex Poole Feb 14 '18 at 20:24
  • So, what data type contains a timezone component. I want to the output to be like that. Can you please change the query accordingly? – Sai Nikhil Feb 14 '18 at 20:29
  • I've done that, but the datetime column.variable/whatever you are converting to a string has to be a data type that knows about time zones for that to work. Using `sysdate` will not work. – Alex Poole Feb 14 '18 at 20:31
  • 2
    @saint1729 - Do you understand the difference between **data type** and **format**? They are very different concepts. If your data uses the type **DATE** then it cannot be formatted to include time zone information, since the Oracle DATE data type is simply not designed/implemented that way. –  Feb 14 '18 at 20:31
2

The format you are requesting doesn't make much sense. +00:00 is the time zone offset (otherwise what is it?) but in Oracle the DATE data type does not know about time zones. Only the Oracle data type TIMESTAMP WITH TIME ZONE should be formatted that way in Oracle.

Here is how this should be done with timestamps WITH TIME ZONE. Note that the standard SYSTIMESTAMP function is a timestamp WITH TIME ZONE. In the query below, you can see how the timestamp is formatted using my session's default, and then using an explicit format model.

SQL> select systimestamp,
  2         to_char(systimestamp, 'dd-Mon-yyyy "T"hh24:mi:sstzh:tzm') as ts
  3  from   dual
  4  ;

SYSTIMESTAMP                                TS
------------------------------------------- ---------------------------
14-FEB-18 12.14.18.537000 PM -08:00         14-Feb-2018 T12:14:18-08:00