just wondering why this first query works, but the second doesn't, if anyone could help please :
SELECT course_no, section_id, to_char(start_date_time, 'Day "The" Ddspth "of" fmMonth yyyy') FROM section
WHERE to_char(start_date_time, 'DY') = 'SUN';[/quote]
WHERE to_char(start_date_time, 'Day') = 'Sunday'; [/quote]
any ideas? can't see what i'm missing?
thanks in advance,
can't see what i'm missing
And we can't see what you mean by "doesn't (work)".
Date format Day returns name of the day blank-padded to length of longest day name (in corresponding language):
SQL> select '[' || to_char(sysdate + 1,'Day') || ']' from dual;
SQL> select '[' || to_char(sysdate + 1,'FMDay') || ']' from dual;
Use FM modifier and ,'nls_date_language=english' to make it NLS independent:
WHERE to_char(start_date_time, 'FMDay','nls_date_language=english') = 'Sunday';
... i find the whole padding thing with certain formats very strange.... don't really see the use for it with some masks, but not others
My guess is that they figured there was no consensus regarding when padding was more useful for different elements. Almost everyone consistently wants 5 seconds padded as '05'; padding isn't wanted quite as often for 5 o'clock, and even less often for the 5th of the month, and (in my experience) padding for days of the week is almost never wanted.. Even if Oracle could determine what most people wanted most of the time, having different padding rules for different elements would confuse everyone. Once you know about 'fm', it isn't hard to use.