RETURN 1-7 iso day NUMBER of week in a function-CodePudding

I could use a bit of help. I'm trying to CREATE a function that returns 1-7 where 1=Monday, 2=Tuesday… 7=Sunday.

I know that 18-JUL-2022 is a Monday and I expect the NUMBER 1 to be returned. Yet I'm getting back 29?

How can I CREATE a function that will RETURN 1-7 only. Why am I getting back 29?


SELECT TO_CHAR( DATE '2022-07-18' ,'IW') FROM DUAL 
29

CodePudding user response：

'iw' in to_char(<date>) is the ISO week number (in the year), not the day number within the ISO week.

Unfortunately, for reasons known only to Oracle, they don't offer a simple date function to return the ISO day of the week.

As a workaround, the same 'iw' option in a different function, 'trunc(<date>)', is helpful in your problem; it truncates the date to the beginning of the ISO week (the Monday of the week, at midnight). Why 'iw' means such different things in two different date functions, only the brilliant developers at Oracle can tell us.

So, you would have to do something a bit tedious, like this (showing it for dates that also have non-zero time-of-day); using a with clause just to generate a bit of testing data:

with
  sample_data (dt) as (
    select to_date('2021-03-04 22:30', 'yyyy-mm-dd hh24:mi') from dual union all
    select to_date('2022-05-30 10:28', 'yyyy-mm-dd hh24:mi') from dual
  )
select to_char(dt, 'Dy dd-Mon-yyyy')   as dt,
       trunc(dt) - trunc(dt, 'iw')   1 as iso_day_of_week
from   sample_data
;

DT                      ISO_DAY_OF_WEEK
----------------------- ---------------
Thu 04-Mar-2021                       4
Mon 30-May-2022                       1

WARNING: to_char(<date>) has an option to show the "day-of-week" - but that is useless to you, since it returns the day-of-week in the locale encoded in NLS settings, not the standard "ISO day of the week" you said you need.

EDIT - per OP's request, here is how the same can be wrapped within a PL/SQL function that can be called from SQL. Note the pragma udf directive (available since Oracle 12) - it makes the code more efficient when the function is called in a SQL context.

create or replace function iso_day_of_week(dt date)
  return number
  deterministic
as
  pragma udf;
begin
  return trunc(dt, 'iw') - trunc(dt)   1;
end;
/

Testing the same on the sample data from above:

with
  sample_data (dt) as (
    select to_date('2021-03-04 22:30', 'yyyy-mm-dd hh24:mi') from dual union all
    select to_date('2022-05-30 10:28', 'yyyy-mm-dd hh24:mi') from dual
  )
select to_char(dt, 'Dy dd-Mon-yyyy') as dt,
       iso_day_of_week(dt)           as iso_day_of_week
from   sample_data
;

DT                      ISO_DAY_OF_WEEK
----------------------- ---------------
Thu 04-Mar-2021                       4
Mon 30-May-2022                       1

CodePudding user response：

'IW' returns week of the year, you need 'D' for the day of the week. Depending on NLS settings, it will return 1 or 2 for Monday.