Day and Month strings contain trailing spaces

When you convert an Oracle Date into a characters string representing the Day or the Month the text strings aren’t quite what you would expect. On a couple of occasions I have written a SQL statement like the following and been left scratching my head

Why hasn’t it returned any Saturdays?
The character string that is returned has trailing white space.

The following actually works but only because ‘WEDNESDAY’ is the longest day string of the week. So really it’s Wednesday’s fault all the other day strings have so much padding =)

How to fix it: You can obviously play with trimming the white space off with string manipulation however I think fm is a far more elegant solution. You can see the effects of fm here. I added a pipe to the end of each string so the amount of padding is really obvious.

About FM
– FM stands for Format Model which will help you find it in the Oracle documentation.
– FM works like a toggle if you include it more than once in the string. Take a look at the last column as an example in the above SQL output.

Alternatively, in the past I have looked into using the day number instead of the day name like this

There’s two drawbacks to this solution
1) The number that represents each day of the week can change. It’s configurable at the session level with the NLS territory.
2) When you read it you don’t instantly know which day of the week you are coding for. I think specifying the full day name is easier to maintain.
Kylie Payne
Dancing With Oracle