OraFAQ Forum: SQL & PL/SQL » How to use TO_CHAR to get week day and filter by weekend days

Home » SQL & PL/SQL » SQL & PL/SQL » How to use TO_CHAR to get week day and filter by weekend days (Oracle 9.2.0.8, Win2003 Server)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to use TO_CHAR to get week day and filter by weekend days [message #304315]

Tue, 04 March 2008 18:22

dmurray
Messages: 5
Registered: July 2007

Junior Member

Hi All,
I need to run a query to fetch workorders that were scheduled to start on a Saturday or Sunday. From the query below you can see I'm using to_char to get the weekday name. What I can't seem to do is filter to get only the Saturday and Sunday records. Any type of clause on "xweekday" variable is throwing a ORA-00904, of course.

Can anyone please advise on the best method of going about this? (pl/sql method perhaps)

query example:
--------------
select targstartdate, actstart, pmnum, To_Char(targstartdate,'DAY') xweekday
from workorder
where worktype='PMRG'
and (targstartdate BETWEEN '01-JAN-06' and '01-JAN-07');

result example:
--------
"TARGSTARTDATE" "ACTSTART" "PMNUM" "XWEEKDAY"
"21-AUG-06" "14-AUG-06" "RPM0280" "MONDAY "
"02-SEP-06" "28-AUG-06" "RPM0040" "SATURDAY "
"19-MAY-06" "29-SEP-06" "RPM0315" "FRIDAY "
"21-APR-06" "29-SEP-06" "RPM0316" "FRIDAY "
"21-APR-06" "29-SEP-06" "RPM0317" "FRIDAY "
"13-JAN-06" "29-SEP-06" "RPM0318" "FRIDAY "

Thanks in advance

Report message to a moderator

Re: How to use TO_CHAR to get week day and filter by weekend days [message #304330 is a reply to message #304315]

Tue, 04 March 2008 21:47

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

You can use an IN clause to check for Saturdays and Sundays, but notice that there are extra spaces after them. By default, Oracle pads them to the length of the longest day, Wednesday. Therefore, it is simpler to use DY which would return SAT or SUN and check for those. Also, you should make sure you use TO_DATE and full years on your strings like TO_DATE ('01-JAN-2006', 'DD-MON-YYYY') in order to avoid problems where the nls_date_format is different.

Report message to a moderator