What is the proper way of formatting date in select query Oracle SQL

saint1729 Source

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
sqloracleoracle11gdate-formattingbi-publisher

Answers

answered 3 months ago Alex Poole #1

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.

answered 3 months ago mathguy #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

comments powered by Disqus