A veces nos interesa saber si el nombre del día de una fecha es Lunes, Martes, Miércoles, etc. Para obtener esa información podríamos usar algo como:

SELECT
   DECODE(
      EXTRACT(
         WEEKDAY FROM MiColumnaFecha), 
            0, 'Domingo', 
            1, 'Lunes', 
            2, 'Martes', 
            3, 'Miércoles', 
            4, 'Jueves', 
            5, 'Viernes', 
            6, 'Sábado')
FROM
   MiTabla

WEEKDAY puede valer 0, 1, 2, 3, 4, 5, 6 dependiendo de si el día es Domingo, Lunes, Martes, Miércoles, Jueves, Viernes o Sábado

Por lo tanto al extraer el WEEKDAY con la  función EXTRACT() obtendremos uno de esos números.

Luego, con la función DECODE() según sea el número devolvemos el nombre del día. Si es 0, entonces el nombre del día es ‘Domingo’. Si es 1 entonces el nombre del día es ‘Lunes’ y así sucesivamente.

Como saber si una fecha es fin de semana

A veces, necesitamos saber si el nombre del día es Sábado o Domingo, por ejemplo si nuestros empleados trabajan de Lunes a Viernes entonces no deberían conectarse a la Base de Datos un Sábado o un Domingo. O si tenemos empleados que trabajan Sábados y Domingos y debemos pagarles más por ese motivo.

SELECT
   MiColumnaFecha
FROM
   MiTabla
WHERE
   EXTRACT(WEEKDAY FROM MiColumnaFecha) IN (0, 6)

En este caso, si el número de WEEKDAY es 0 (Domingo) o si es 6 (Sábado) la fecha cae en fin de semana.

Un stored procedure para obtener el nombre del día

Para ahorrarnos trabajo y no estar escribiendo la misma cosa una y otra vez, lo más sencillo es tener un stored procedure que nos devuelva el nombre del día de la semana, como el siguiente:

CREATE PROCEDURE NOMBRE_DIA(
   tdFechax DATE)
RETURNS(
   tcNombreDia VARCHAR(9))
AS
BEGIN

   tcNombreDia = (DECODE(
                     EXTRACT(
                        WEEKDAY FROM tdFechax), 
                           0, 'Domingo', 
                           1, 'Lunes', 
                           2, 'Martes', 
                           3, 'Miércoles', 
                           4, 'Jueves', 
                           5, 'Viernes', 
                           6, 'Sábado'));

   SUSPEND;

END;

Al cual podríamos llamar así:

EXECUTE PROCEDURE NOMBRE_DIA(CURRENT_DATE);

o si lo necesitamos dentro de un SELECT entonces así:

SELECT
   Nombre_Dia(MiColumnaFecha)
FROM
   MiTabla