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)
How to use TO_CHAR to get week day and filter by weekend days [message #304315] Tue, 04 March 2008 18:22 Go to next message
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
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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.
Re: How to use TO_CHAR to get week day and filter by weekend days [message #304331 is a reply to message #304315] Tue, 04 March 2008 21:51 Go to previous messageGo to next message
mshrkshl
Messages: 247
Registered: September 2006
Location: New Delhi
Senior Member
use date functions(next_day).

regards,
Re: How to use TO_CHAR to get week day and filter by weekend days [message #304812 is a reply to message #304315] Thu, 06 March 2008 16:25 Go to previous message
dmurray
Messages: 5
Registered: July 2007
Junior Member
Thanks all. I appreciate the help!
Previous Topic: summation of result of two queries
Next Topic: UNION SELECT which removes results from the second table if they exist in the first
Goto Forum:
  


Current Time: Fri Dec 09 15:39:54 CST 2016

Total time taken to generate the page: 0.24121 seconds