Has anyone noticed that if you have the following in a query or anywhere that the results are not to your satisfaction. to_char(all_dates.adate,'DAY MONTH DD YYYY') This statment will put in uneccessary spaces in between the Day, Month, and Day number. Does anyone know a better way to get a date to say Friday April 01 1983 ? Thanks Mark
Here we go. SQL> SELECT TO_CHAR(SYSDATE, 'fmDAY MONTH DD YYYY') date_but_no_space 2 FROM dual; DATE_BUT_NO_SPACE --------------------------- WEDNESDAY MAY 1 2002 Is this what you want? If not let us know. -Bal
Anup Batra
Ranch Hand
Joined: Jan 21, 2002
Posts: 41
posted
0
Good day to u Mark, This is the first time I am answering, I might be wrong. Pls correct where I have gone wrong. Actually all this is happening due to the default functionality of Oracle i.e. default format in which date will be presented to the user. In ur to_char string to_char(all_dates.adate,'DAY MONTH DD YYYY') DAY will occupy 8 character space since longest day is Thursday or Saturday , it will actually provide enough space so that if the day would be any of these it will be adjusted properly. So if the day is Friday then it look that there are too many space left unoccupied but actually these spaces are occupied (by DAY format) so that days like Thursday or Saturday can be easily adjust without creating a unreadable date. Same is the case with all the others i.e. months (remember MAY and September the smallest and longest). To avoid these there is something known as format Mask. I think there are only 2 that is 1.Fx 2.Fm Fm if written in front of date format will remove the extra spaces as explained above Example First without format: select to_char(emp.hiredate, 'DAY MONTH DD YYYY') from emp; TO_CHAR(EMP.HIREDATE,'DAYMO --------------------------- MONDAY DECEMBER 17 2001 FRIDAY FEBRUARY 20 1981---------------------� see the difference here THURSDAY APRIL 02 1981 Now with Fm format: select to_char(emp.hiredate, 'Fm DAY MONTH DD YYYY') from emp; Result TO_CHAR(EMP.HIREDATE,'FMDAYM ---------------------------- MONDAY DECEMBER 17 2001 FRIDAY FEBRUARY 20 1981 SUNDAY FEBRUARY 22 1981 THURSDAY APRIL 2 1981 MONDAY SEPTEMBER 28 1981 ----------------� and now the change FRIDAY MAY 1 1981 TUESDAY JUNE 9 1981 SUNDAY APRIL 19 1987
I hope so this explanation will clear the problem. Bye Anup Batra
0
