How to use month and year in to_char in oracle?-CodePudding

Quick help on this line of my code in my oracle database. So, I have a to_char with sysdate. However, I want to change the sysdate to say Jul-2020 but for some reason it tells me invalid number. Can anyone help me solve this small issue? thanks for the help.

here is what I have:

Before:

to_char(sysdate, 'YYYY')

After:

to_char('Jul-2020', 'MM-YYYY'

CodePudding user response：

The problem is first you have let the dB know "Jul-2020" is a date format so the correct line should be to_char(to_date('Jul-2020','Mon-yyyy'), 'MM-YYYY')

CodePudding user response：

Something along these lines should work as long as you provide input dates as below. You just need to be consistent, meaning you can't do 2020-July without changing output format to YYYY-MM

select to_char(to_date('07-2020','MM-YYYY'),'MM-YYYY') from dual;

select to_char(to_date('July-2020','MM-YYYY'),'MM-YYYY') from dual;

If you want to be able to use both sysdate and hardcoded values inter-changeably, you can provide date in a specific format that works for sysdate and hardcoded date

select to_char(to_date(sysdate,'DD-MM-YYYY'),'MM-YYYY') from dual;

select to_char(to_date('01-07-2020','DD-MM-YYYY'),'MM-YYYY') from dual;

select to_char(to_date('01-July-2020','DD-MM-YYYY'),'MM-YYYY') from dual