CODEWITHSUNDEEP
datetimeplsqloracle-sqldeveloperplsqldeveloper
MontyPython
MontyPython

Reputation: 2994

How to remove weekend non-working hours in PL/SQL?

IF (To_Char(Date1,'Day') = 'Sunday' Or To_Char(Date2,'Day')  = 'Sunday') Then
SELECT columns ... FROM Table_Name;

ELSE
SELECT columns ... FROM Table_Name;

This is not working!

Upvotes: 1

Views: 359

Answers (1)

Ben
Ben

Reputation: 52893

It's "not working" because you're not using the format model modifier, fm, in your format model. Because you're not using the format model modifier the days of the week are right padded with spaces to 9 characters (the length of the longest Wednesday). This in turn implies that your test for equality is incorrect.

In order to fix it use the format model modifier:

to_char(date1, 'fmDay')

You can run this query for a demonstration of both the problem and the solution:

with a_week as (
  select sysdate - level as dy
    from dual
 connect by level <= 7 )
select dy
     , to_char(dy, 'Day')
     , length(to_char(dy, 'Day'))
     , to_char(dy, 'fmDay')
     , length(to_char(dy, 'fmDay'))
  from a_week

See this SQL Fiddle

Having said that I wouldn't bother. Why do you need to type out the entire day? Use the DY format model instead:

if to_char(date1,'DY') = 'SUN' or to_char(date2,'DY') = 'SUN' then
   ...

This is always three characters long.

One quick note on debugging. If you know the first IF statement is not being executed properly I would follow these stages:

  1. Take out the test from the IF statement and put it into a SELECT. Check that the two things you are testing for equality are identical.
  2. Once you've found out they're not you know that the format model is the problem. You might not know it's called the format model but you're using it in TO_CHAR() so you head for the documentation on that.
  3. The TO_CHAR() documentation leads you to format models where you can find format model modifiers.

Upvotes: 2

Related Questions