This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes Oracle/OAS and the fly likes Dates showing up weird in to_char Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "Dates showing up weird in to_char" Watch "Dates showing up weird in to_char" New topic
Author

Dates showing up weird in to_char

Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

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


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Bal Sharma
Ranch Hand

Joined: Sep 19, 2001
Posts: 273
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
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
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Thanks guys. fm is what I needed.
Mark
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Dates showing up weird in to_char
 
Similar Threads
First day of the first week of the month
Select date field problem
how to count total number of records in each group , when using group by statement.
Query giving me two different results
Query Needed - please help