The following methods avoid using the Fiscal Period tables where simple dates will serve. I have found the methods below to be particularly useful in creating Batch Journal Voucher transactions.

Periodically run reports, extracts, and table loads can often be fully automated using Oracle's Date Functions. For example, a cron scheduled Unix script can be run the 1st day of each month. However, most programs I write require a date range or a period ending date to select the correct records or specify the correct period for entry. Batch Journal Vouchers often require a transaction date set to the last day of the month just ended. Whenever possible, I use the current system date to determine the date or date range I need.

Using such methods can even work for user-initiated jobs. I have found that user's sometimes run month end programs before and sometimes after the month end. You can code around this by determining the month end nearest the current date. Avoiding entry of dates reduces the chance of entry errors.

Note that the entire section below is SQL*Plus code:

-- Banner's date format standard is 'DD-MON-YY', but reports are easier with: 
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

-- While much of this could be more succinct, the following is easier to understand:

-- Abbreviations used below: 
-- CD - Current Date
-- CM - Current Month
-- LM - Last Month
-- LDCM - Last Day Current Month
-- LDLM - Last Day Last Month

-- Store the Current Date
COLUMN CD NEW_VALUE CD FORMAT A11 TRUNC
SELECT SYSDATE CD FROM DUAL;

-- Store the Current Month
COLUMN CM NEW_VALUE CM FORMAT A2 TRUNC
SELECT TO_CHAR(TO_DATE('&&CD'), 'MM') CM FROM DUAL;

-- Store the Last Month
COLUMN LM NEW_VALUE LM FORMAT A2 TRUNC
SELECT DECODE(TO_CHAR(TO_NUMBER('&&CM')-1),'0','12',LPAD(TO_CHAR(TO_NUMBER('&&CM')-1),2,'0')) LM FROM DUAL;

-- Store the Month End Dates

-- Last Day Current Month
COLUMN LDCM NEW_VALUE LDCM
SELECT LAST_DAY(TO_DATE('&&CD')) LDCM FROM DUAL;

-- Last Day Last Month
COLUMN LDLM NEW_VALUE LDLM
SELECT LAST_DAY(LAST_DAY(TO_DATE('&&CD'))-32) LDLM FROM DUAL;

-- Using a calculated date 32 days before the last day of any month always gives you a day in the prior month.

-- Manually initiated scripts frequently need the nearest Month End Date
-- If the Day from SYSDATE is near (<16th) the Beginning of the Month
-- use the Last Day of Last Month else, use the Last Day of This Month

COLUMN LAST_OR_THIS NEW_VALUE LAST_OR_THIS
SELECT DECODE(DECODE(SIGN(15-TRUNC(TO_CHAR(TO_DATE('&&CD'),'DD'))),-1,'END','BEGIN'),'END','&&LDCM','BEGIN','&&LDLM','&&LDLM') Last_or_This FROM DUAL;

-- This might be more easily followed written as:
SELECT 
DECODE(
DECODE(SIGN(15-TRUNC(TO_CHAR(TO_DATE('&&CD'),'DD'))),1,'END','BEGIN'),
'END','&&LDCM',
'BEGIN','&&LDLM',
'&&LDLM') Last_or_This FROM DUAL;

-- For date ranges you can frequently calculate the Begin Date from the Period End Date:
-- The first day of the month for month ending Last_or_This
COLUMN Begin_Date NEW_VALUE Begin_Date
SELECT '01-'||TO_CHAR(TO_DATE('&&Last_or_This'),'Mon-YYYY') Begin_Date FROM DUAL;

-- Several date formats commonly used in transaction descriptions:

-- Store Month End Date in variable Trans_Date
COLUMN Trans_Date NEW_VALUE Trans_Date
SELECT '&&Last_or_This' Trans_Date FROM DUAL;

-- Store Month_Year (Date in Month + YYYY Format, e.g., August 2000)
COLUMN Desc_Month_Year NEW_VALUE Desc_Month_Year
SELECT TO_CHAR(TO_DATE('&&Trans_Date'),'MonthYYYY') Desc_Month_Year FROM DUAL;

-- Or for better spacing between the month and year (e.g., August 2000):
SELECT RTRIM(TO_CHAR(TO_DATE('&&Trans_Date'),'Month'))||
' '||TO_CHAR(TO_DATE('&&Trans_Date'),'YYYY') Desc_Month_Year FROM DUAL;

-- Store Month_Year (Transaction Date in Mon + YY Format, e.g., Aug00)
COLUMN Month_Year NEW_VALUE Month_Year
SELECT TO_CHAR(TO_DATE('&&Trans_Date'),'MonYY') Month_Year FROM DUAL;


-- Programmers from non-Oracle environments will likely have made use of Julian Dates for performing Date Math.  While Oracle Date math is simple, one must convert dates to numbers for math operations in many other programming tools.  For conversions of application code without rewriting the application, the following formats are very helpful:

Getting a Julian Date/Number is pretty easy.

select TO_CHAR(SYSDATE,'J') from dual;

As is converting the Julian number to an Oracle Date:

select TO_DATE(TRUNC(2454228),'J') "Date for Number" from dual;

We sometimes speak of the day of the year as being the Julian Date, well not really, but there is also a Format for the Day of the Year, e.g.: TO_CHAR(SYSDATE,'DDD')


Oracle Dates always contain both date and time.  You can use Date differences to calculate elapsed time:

e.g., the number of minutes between 9:00 am and 11:50 am:

SELECT (TO_DATE('1150','HH24MI')-TO_DATE('0900','HH24MI'))*1440 MINUTES_BETWEEN FROM DUAL;

MINUTES_BETWEEN
---------------
170

To help "see" the date and time, try
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYYHH24MISS';
to force the default display to show the time.

Disclaimer

Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences.  The scripts and information are provided "as is" without warranty, implied or otherwise.  Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:)