Skip to Main Content
  • Questions
  • Output is not as per date format model

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Ankur.

Asked: March 11, 2021 - 5:11 pm UTC

Last updated: March 18, 2021 - 7:28 am UTC

Version: 19.8.0.0.0

Viewed 1000+ times

You Asked

Select To_Char(to_date('31-Mar-2020'), 'Wth') As A, To_Char(to_date('31-Mar-2020'), 'WTH') As B, 
       To_Char(to_date('01-Feb-2020'), 'WWth') As C, To_Char(to_date('01-Feb-2020'), 'WWTH') As D
from dual;

Ouput

A B C D
5th 5TH 05TH 05TH


--Expected Output--
th in A's output is in small letter. [Expected]
TH in B's output is in capital letter. [Expected]

--Unexpected Output--
TH in C's output is in Capital letter. [Not expected]
TH in D's output is in capital letter. [Expected]


Question: For C column I deliberately wrote 'WWth' to get "th" in small letter but it is showing "TH" which is unexpected output which is against format model.

and Connor said...

The "th" is called a "modifier" to a format mask rather than a format mask in its own right. The format mask is the "WW", which is in upper case, and hence we assume upper case throughout. In that sense, the Wth is a bug rather than the other way around.

In any event, the case of leading element can be used to control things, eg

SQL> select to_char(sysdate,'DDth') from dual;

TO_C
----
17TH

SQL> select to_char(sysdate,'ddth') from dual;

TO_C
----
17th


Rating

  (1 rating)

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

Comments

Thanks

A reader, March 17, 2021 - 5:57 am UTC

Thank you very much Mr. Connor.
Connor McDonald
March 18, 2021 - 7:28 am UTC

glad we could help

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