1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| WITH T AS (SELECT TO_DATE('01.08.2007', 'DD.MM.YYYY') + ROWNUM - 1 d,
2 AS nbsem,
'Tuesday' AS jour
FROM dual)
SELECT d, TO_CHAR(d, 'DAY'), TO_CHAR(d, 'D'),
TRUNC(d, 'MM') + 1 - TO_CHAR(TRUNC(d, 'MM'),'D') prem_dimanche_mois, --premier dimanche du mois
TRUNC(d, 'MM') + 1 - TO_CHAR(TRUNC(d, 'MM'),'D') + 7 * (:nbsem -1) AS prem_dimanche_semaine, -- premier dimanche de la semaine
NEXT_DAY(TRUNC(d, 'MM') + 1 - TO_CHAR(TRUNC(d, 'MM'),'D') + 7 * (nbsem -1) -1, 'Sunday') AS prochain_dimanche_delasemaine,
NEXT_DAY(TRUNC(d, 'MM') + 1 - TO_CHAR(TRUNC(d, 'MM'),'D') + 7 * (nbsem -1) -1, jour) AS prochain_mardi_delasemaine
FROM t
D 01/08/2007
TO_CHAR(D,'DAY') WEDNESDAY
TO_CHAR(D,'D') 4
PREM_DIMANCHE_MOIS 29/07/2007
PREM_DIMANCHE_SEMAINE 05/08/2007
PROCHAIN_DIMANCHE_DELASEMAINE 05/08/2007
PROCHAIN_MARDI_DELASEMAINE 07/08/2007 |
Partager