Date format in oracle
Hai,
From the database, I only want to take the month from the whole date format. Although I am getting the month as 08 Aug and 09 Sep etc. But I need to get the month like 8 for August and 9 for September, etc.
How can i do this? Can anyone help me?
select to_number(to_char(sysdate, 'mm')) from dual
This query returns 09:
select to_char(sysdate, 'MM') from dual
This query returns 9:
select ltrim(to_char(sysdate, 'MM'),'0') from dual
Try this using just one function call:
select to_char(sysdate, 'FMMM') from dual;
For details on the "FM" syntax, see Format Model Modifiers .
One important thing to know about an FM operator is that it basically functions as a "switch". Think of the FM element as a prefix that acts on the entire element of the datetime element.
So in this case, when you only want the month, FMMM says essentially "return MM (month between 1 and 12), but leave trailing zeros".
If, for example, you used:
select to_char(sysdate, 'FMMMDD') from dual;
which will be interpreted to "return MMDD (month between 1 and 12, day between 1 and 31), but leave leading zeros for both month and day left blank .
If your date was August 8th, for example
select to_char(sysdate, 'MMMDD') from dual;
will return
0808
adding operator FM
select to_char(sysdate, 'FMMMDD') from dual;
will return
88
Notice how FM works throughout the entire datetime element.