Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question.

Asked: July 19, 2016 - 12:32 pm UTC

Last updated: July 20, 2016 - 3:03 pm UTC

Version: 10.0

Viewed 1000+ times

You Asked

--->A simple SQL issue that I faced few days back.

We normally perform some executions on weekends(SATURDAY or SUNDAY) like some sort of cleaning activity,stats gathering,partition creation etc. Please take caution If your are planning to achieve this using PL/SQL objects with conditions as
IF to_char(sysdate,'DAY') = 'SATURDAY' then .....END IF;
Because the condition will never match.
Reason:- length(to_char(sysdate,'DAY')) will always be 9(length of 'WEDNESDAY')

So in this case what i can do?....instead of to_char what i can prefer.

and Chris said...

There are various options available. You could:

- Right pad your day strings with spaces up to 9 characters
- Use the FM mask to remove trailing spaces
- Use the DY mask and compare to the first three characters of the days (MON, TUE, WED, ...)

For example:

SQL> with rws as (
  2    select sysdate+rownum dt from dual connect by level <= 7
  3  )
  4    select *
  5    from   rws
  6    where  to_char(dt, 'DAY') = rpad('MONDAY', 9, ' ');

DT
---------
25-JUL-16

SQL>
SQL> with rws as (
  2    select sysdate+rownum dt from dual connect by level <= 7
  3  )
  4    select *
  5    from   rws
  6    where  to_char(dt, 'FMDAY') = 'MONDAY';

DT
---------
25-JUL-16

SQL>
SQL> with rws as (
  2    select sysdate+rownum dt from dual connect by level <= 7
  3  )
  4    select *
  5    from   rws
  6    where  to_char(dt, 'DY') = 'MON';

DT
---------
25-JUL-16

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, July 20, 2016 - 9:03 am UTC

to_char(sysdate,'D') (numeric day of week) should be simplest, but beware of the NLS_TERRITORY setting if the application needs to run both in territories which follow the ISO standard of Monday as day 1 and territories like the US; Canada or Israel which start the week on Sunday. But in that case it should be easy to encapsulate necessary calculations this in a small function.
Chris Saxon
July 20, 2016 - 9:27 am UTC

I'd avoid to_char(d, 'D') for the reasons you say. I've seen too many people caught out by this.

While it is easy to place in a function, many just don't know to do this.

Magic of Trim

Manjunath Hegde, July 20, 2016 - 10:07 am UTC

I think that's where the magic of trim comes into picture.

below will work just fine.

select trim(to_char(sysdate-1,'DAY')) from dual
Chris Saxon
July 20, 2016 - 12:53 pm UTC

Yep, that works too.

territory and language agnostic

MatteoP, July 20, 2016 - 2:49 pm UTC

To avoid dependencies from language and territory settings choose a date that is known to be sunday, e.g. 01/01/2012,
calculate the difference from your date of interest, then compute modulus 7
with md as
(select trunc(sysdate - level) mydays , mon
from (select to_date('01/01/2012','dd/mm/yyyy') mon from dual ),
dual
connect by level <= 30
)
select mydays, -- mydays - mon diffdays,
       mod((mydays - mon), 7) dow
from md


DOW = 0 --> sunday
DOW = 6 --> saturday

regardless of NLS settings

MYDAYS DOW
19/07/2016 2
18/07/2016 1
17/07/2016 0
16/07/2016 6
15/07/2016 5
14/07/2016 4
13/07/2016 3
12/07/2016 2
11/07/2016 1
10/07/2016 0
...



Chris Saxon
July 20, 2016 - 3:03 pm UTC

Wellll, you could do that. But it's fiddly. To have language independence you can pass the nls_date_language parameter to to_char:

SQL> alter session set nls_date_language=french;

Session altered.

SQL>
SQL> with rws as (
  2    select sysdate+rownum dt from dual connect by level <= 7
  3  )
  4    select to_char(dt, 'DY')
  5    from   rws
  6    where  to_char(dt, 'DY', 'nls_date_language=english') = 'MON';

TO_CHAR(DT,'DY')
----------------
LUN.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.