Date Formatting

There are two basic data types for storing a date time value, DATE and TIMESTAMP. DATE is the most commonly used and TIMESTAMP is used when fractions of seconds needs to be stored.

Contents

Oracle DATE data type

The DATE data type stores the year (including the century), the month, the day, the hours, the minutes, and the seconds (after midnight).
Oracle Database can store dates ranging from January 1, 4712 BCE through December 31, 9999 CE (Common Era, or 'AD').

For input and output of dates, the standard Oracle date format is DD-MON-YY, as follows: '12-DEC-14' . This default date format is controlled by the NLS_DATE_FORMAT Oracle parameter.

To enter dates that are not in default date format, it is possible to use the built in Oracle function TO_DATE with a format mask: TO_DATE ('December 12, 2014', 'MONTH DD, YYYY')

IFS Applications allows a specific NLS_DATE_FORMAT to be specified per language, see Used Languages.

To get correctly formatted dates and times, the DATABASE_SYS package functions Get_Formatted_Date, Get_Formatted_DateTime or Get_Formatted_Time should be used. These functions will use the NLS_DATE_FORMAT for the current language

FUNCTION Get_Formatted_Date (
	date_ IN DATE DEFAULT Sysdate ) RETURN VARCHAR2; 

FUNCTION Get_Formatted_Datetime (
	date_ IN DATE DEFAULT Sysdate ) RETURN VARCHAR2;

FUNCTION Get_Formatted_Time (
	date_ IN DATE DEFAULT Sysdate ) RETURN VARCHAR2;

Example

 

Oracle Database stores time in 24-hour format—HH:MI:SS. By default, the time in a date field is 00:00:00 A.M. (midnight) if no time portion is entered. To enter the time portion of a date, use the TO_DATE function with a format mask indicating the time portion, as in: INSERT INTO birthdays (bname, bday) VALUES ('JOHN',TO_DATE('12-DEC-80 09:30 A.M.','DD-MON-YY HH:MI A.M.'));
 

Oracle Timestamp data type


The TIMESTAMP data type is an extension of the DATE data type. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE datatype. The fractional part is by default stored with 6 digits. The Oracle parameter NLS_TIMESTAMP_FORMAT defines the timestamp format when a character string is converted to the TIMESTAMP data type.

You can specify the TIMESTAMP literal in a format like the following:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF' which gives a date time like '2014-12-31 12:56:50.18'


IFS Applications allows a specific NLS_TIMESTAMP_FORMAT to be specified per language, see Used Languages.

To get a correct formatted  TIMESTAMP, the DATABASE_SYS package functions Get_Formatted_DateTime or Get_Formatted_Time  should be used. These functions will use the NLS_TIMESTAMP_FORMAT for the current language