This blog post discusses a package of useful date functions, written in PL/SQL.
Click on the following link view the PL/SQL source code.
The package contains the following functions:
| UTIL_DATE Functions | ||
|---|---|---|
| Function Name | Arguments | Description |
| count_day_of_week | p_date_start: Start counting from this date.
,p_date_end: Stop counting at this date. ,p_dayno: Number indicating week day, 1=Monday, 2=Tuesday etc. |
Returns a number, count of occurences of a given weekday between 2 dates. |
| is_a_working_day | p_date: Date to be checked.
,p_country_id: Country to check holidays for. The table COUNTRY_HOLIDAY defines the dates of public holidays for each year. ,p_saturday_workday: Boolean, TRUE if Saturdays are working days. ,p_sunday_workday: Boolean, TRUE if Sundays are working days. |
Returns a boolean value TRUE if the date is a working day, otherwise FALSE. |
| first_day | p_date: A valid date in a month for which you want the start date. | Returns date of first day of the specified month. |
| first_day_month | p_date: A valid date in the target month.
,p_dayno: Number indicating week day, 1=Monday, 2=Tuesday etc. |
Returns date on which the specified day first occurs. |
| last_day_month | p_date: A valid date in the target month.
,p_dayno: Number indicating week day, 1=Monday, 2=Tuesday etc. |
Returns date on which the specified day last occurs in the target month. |
| first_workday_month | p_date: A valid date in the target month.
,p_country_id: Country to check holidays for. The table COUNTRY_HOLIDAY defines the dates of public holidays for each year. ,p_saturday_workday: Boolean, TRUE if Saturdays are working days, FALSE if non-work days. ,p_sunday_workday: Boolean, TRUE if Sundays are working days, FALSE if non-work days. |
Returns date of first working day in the month. |
| last_workday_month | p_date: A valid date in the target month.
,p_country_id: Country to check holidays for. The table COUNTRY_HOLIDAY defines the dates of public holidays for each year. ,p_saturday_workday: Boolean, TRUE if Saturdays are working days, FALSE if non-work days. ,p_sunday_workday: Boolean, TRUE if Sundays are working days, FALSE if non-work days. |
Returns date of last working day in the month. |
| working_days | p_date_start: Start date of range.
,p_date_end: End date of range. ,p_country_id: Country to check holidays for. The table COUNTRY_HOLIDAY defines the dates of public holidays for each year. ,p_saturday_workday: Boolean, TRUE if Saturdays are working days, FALSE if non-work days. ,p_sunday_workday: Boolean, TRUE if Sundays are working days, FALSE if non-work days. |
Returns a count of the number of working days within the specified date range. |
| easter_friday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Easter Friday |
| easter_saturday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Easter Saturday. |
| easter_sunday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Easter Sunday. |
| easter_monday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Easter Monday. |
| shrove_tuesday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Shrove Tuesday. |
| ash_wednesday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Ash Wednesday. |
| palm_sunday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Palm Sunday. |
| whitsunday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Whitsunday. |
| whit_monday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Whit Monday. |
| ascension_day | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Ascension. |
| corpus_christi | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Corpus Christi. |
| mardi_gras | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Mardi Gras. |
| carnival_monday | p_year: Year number 4 digits, e.g. 2024. | Returns the date of Carnival Monday. |
The function easter_sunday returns the date of Easter Sunday for a given year.
SELECT util_date.easter_sunday(2024)
FROM dual;
31-MAR-24
ACCEPT p_year NUMBER FORMAT '9999' PROMPT 'Enter a year:';
SELECT 'Easter in ' || &p_year ||
' falls on: ' ||
to_char(util_date.easter_sunday(&p_year),'Dy Mon DD YYYY')
FROM dual;
 |
 |