This article gives you all the information you need in order to begin working with dates in your PL/SQL programs.
Dates, Time Stamps, and Intervals in PL/SQL Most applications require the storage and manipulation of dates and times.
A default installation of Oracle Database sets the default DATE format to DD-MON-YYYY.
The default TIMESTAMP format includes both the date offset and the time zone offset. As with TO_CHAR for numbers, you use another version of the TO_CHAR function to convert a date or a time stamp to a string.
The most common use of TRUNC is TRUNC (date)—without any format mask specified. You can also use TRUNC to easily obtain the first day in a specified period.
Here are some TRUNC examples: The second argument must be a day of the week in the date language of your session (specified by NLS_DATE_LANGUAGE), provided as either the full name or the abbreviation.
It has multiple parts (year, month, day, hour, and so on), and there are many rules about what constitutes a valid date.
Code Listing 2: Calls to SYSDATE and SYSTIMESTAMP and the returned values Because I have passed dates and time stamps to DBMS_OUTPUT.
PUT_LINE, Oracle Database implicitly converts them to strings, using the default format masks for the database or the session (as specified by the National Language Settings NLS_DATE_FORMAT parameter).
Following are the Datetime data types − It stores date and time information in both character and number datatypes.
It is made of information on century, year, month, date, hour, minute, and second.
Unlike strings and numbers, dates are quite complicated: not only are they highly formatted data, but there are also many rules for determining valid values and valid calculations (leap days and years, daylight saving time changes, national and company holidays, date ranges, and so on).