Manipulating with SQL query output

Question

I want to write a query which returns the month only in 3 characters but the first character should be Capital and the rest non-capital. For example DEC should be (Dec) or JAN (Jan).

select substr(sysdate,4,3) from dual;

SQL code above returns the month but in capital "DEC", so I want the output as "Dec".

Does anyone know how to achieve it?

Thanks

Don't use substr on a DATE column. That relies on the evil implicit data type conversion. On my computer your query would return 8-1 — user330315
– user330315, Commented Dec 3, 2018 at 8:37

Barbaros Özhan · Accepted Answer · 2018-12-03 08:40:31Z

3

There is no need for manipulation, as stated in previous answers.

You can simply write this:

SELECT TO_CHAR (SYSDATE, 'Mon', 'nls_date_language=American') FROM DUAL;

If you'd pass 'Month' as the second parameter you'd get 'December', if you'd pass 'month' you'd get 'december' and if you pass 'Mon' you will get 'Dec', as you have asked.

I hope I helped!

edited Dec 3, 2018 at 8:40

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Dec 3, 2018 at 8:37

Goran Kutlaca

2,0241 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

D-Shih · Accepted Answer · 2018-12-03 08:30:15Z

2

You can try to use substr with to_char function.

select substr(to_char(sysdate, 'Month', 'nls_date_language=American') ,0,3)  from dual

c# online

Note

use nls_date_language=American to set culture in to_char function.

answered Dec 3, 2018 at 8:30

D-Shih

46.5k6 gold badges37 silver badges56 bronze badges

Comments

Nikhil · Accepted Answer · 2018-12-03 09:57:29Z

0

this will work indeed jus to use an extra function that is initials with caps on :

select initcap(substr(sysdate,4,3)) from dual;

edited Dec 3, 2018 at 9:57

answered Dec 3, 2018 at 8:30

Nikhil

3,9785 gold badges20 silver badges34 bronze badges

1 Comment

user330315 Over a year ago

Don't use substr on a DATE column. That relies on the evil implicit data type conversion. On my computer this would return 8-1

Collectives™ on Stack Overflow

Manipulating with SQL query output

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related