A Free Trial That Lets You Build Big!
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
Using date types in PL/SQL
The previous article described the use of strings and numbers in PL/SQL. There is no doubt that strings and numbers are important, but you can be sure that no application is dependent on the date.
You need to record events, people's date of birth and many more.
The usual way to use this is:
1) Declaring date variables and constants
2) Use the built-in function to display and modify date values
3) Execution date related calculations
Date types are more complex than strings or numbers. It has multiple parts (years, months, days, hours, minutes, seconds, etc.), and there are many rules about a valid date.
This article will give you all the information to use the date in your PL/SQL program.
1, date in PL/SQL, timestamp and interval (intervals)
Most applications need to store and manipulate the date and time. Unlike strings and numbers, date requirements are more complex: not just because they are more advanced format data, but for valid values and effective calculations
Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time inform ation in a internal format, and they also has an extensive set of built-in functions for manipulating the date a nd time.
Fortunately, Oracle database and PL/SQL provide a range of date and time types to store date and time information in a standard form, as well as a series of built-in functions for date and time calculations.
There are three types of correlation:
1) Date-this datatype stores a DATE and a time, resolved to the second. It does not include the time zone "timezone". DATE is the oldest and most commonly used datatype for working with dates in Oracle applications. is also the oldest and most commonly used type.
2) Timestamp-time stamps is similar to dates, but with these-key distinctions:
(2.1) You can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), an D
Storage accuracy of 1 billion per second.
(2.2) You can associate a time zone with a time stamp, and Oracle Database would take this time zone into account when Mani Pulating the time stamp. Available with time zone.
3) Interval-whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify a interval in terms of years and months, or days and seconds.
Record the time interval. You can specify the time interval for the year and month or day and second levels.
Let's look at an example:
Code Listing 1:declaring DATE, TIMESTAMP, and INTERVAL variables
DECLARE l_today_date DATE := SYSDATE; l_today_timestamp TIMESTAMP := SYSTIMESTAMP; l_today_timetzone WITHTIME ZONE := SYSTIMESTAMP; l_interval1 YEAR (4TOMONTH‘2011-11‘; l_interval2 DAY (2TOSECOND‘15 00:30:44‘;BEGIN null;END;/
2, how to choose the appropriate date type?
1) Use one of the TIMESTAMP types if your need to track time down to a fraction of a second.
2) You can, in general, use the TIMESTAMP in place of DATE. A time stamp this does not contain Subsecond precision takes up 7 bytes of storage, just as a DATE datatype does. When your time stamp does contain subsecond "sub-second" data, it takes up bytes of storage.
3) Use TIMESTAMP with time zone if need to keep track of the session time zone in which the data is entered.
4) Use TIMESTAMP with LOCAL time ZONE if you want the database to automatically convert a time between the database and SES Sion time zones. Automatically convert database time and session time time zone.
5) Use DATE when it's necessary to maintain compatibility with a existing application written before any of the TIMESTAMP Datatypes were introduced. Keep forward compatibility.
3. How to get the current time?
It is believed that most developers pass the Sysdate function, but the Oracle database also provides some other functions to take a look at:
1) session-level functions:
Current_date return: DATE
Current_timestamp return: TIMESTAMP with time ZONE
Localtimestamp return: TIMESTAMP
2) database level
Sysdate return: DATE
Systimestamp return: TIMESTAMP with time ZONE
Code Listing 2:calls to Sysdate and Systimestamp and the returned values
BEGIN DBMS_OUTPUT.put_line (SYSDATE); DBMS_OUTPUT.put_line (SYSTIMESTAMP); DBMS_OUTPUT.put_line (SYSDATE - SYSTIMESTAMP);END;/
Here is the output:
07-aug-11 08.46.16.379000000 am-05:00
Because I gave dbms_output. Put_Line passes the date and time stamp, and the Oracle database implicitly converts it to a string using the database or session-level default format (parameter: Nls_date_format).
The default format for installing the database is: dd-mon-yyyy. The default timestamp format contains the date and time zone offset (offset).
How to modify? "Translator Note: The following excerpt from the network"
1). You can specify (LINUX) in the user environment variable. Add two sentences to the user's. Bash_profile:
Export nls_lang=american-Note that this sentence must be specified, otherwise the next sentence will not take effect.
Export nls_date_format= ' Yyyy/mm/dd hh24:mi:ss '
2). Add a sentence in the Sqlplus glogin.sql file: Alter session Set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';
3). Modify the date format of the current session directly: Alter session set Nls_date_format = ' Yyyy-mm-dd hh24:mi:ss ';
4). Modify the database parameters, need to restart the database after the effective sql> alter system set nls_date_format= ' Yyyy-mm-dd hh24:mi:ss ' scope=spfile;
4. Implement date-to-string and string-to-date conversions?
As with the To_char function for numbers, we use a different version of To_char to implement the conversion of a date or timestamp type to a string.
If a to_char with no format parameters is used. The database uses an implicit conversion.
BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE)); DBMS_OUTPUT.put_line ( TO_CHAR (SYSTIMESTAMP));END;/ 07-AUG-1107-AUG-11 08.55.00.470000000 AM -05:00
Use To_char to display the full names of both, the day, and the month in the date:
BEGIN DBMS_OUTPUT.put_line (TO_CHAR (SYSDATE, ‘Day, DDth Month YYYY‘));END;/Sunday , 07TH August 2011
Note:the language used to display these names are determined by the Nls_date_language setting, which can also be specified As the third argument in the To_char, as in
Note: the date language displayed by the term is determined by the parameter: Nls_date_language. It can be used as the 3rd parameter of To_char:
BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, ‘Day, DDth Month YYYY‘, ‘NLS_DATE_LANGUAGE=Spanish‘));END;/Domingo , 07TH Agosto 2011
In addition, I can use the FM element modifier in order to remove extra 0 and spaces from the display results.
BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, ‘FMDay, DDth Month YYYY‘));END;/Sunday, 7TH August 2011
You can also use the format mask to extract just a portion of, or information on, the date, as shown in the following E Xamples:
You can also use the format parameter to extract only part of the date:
What quarter is it? The next moment of the current time?
To_char (sysdate, ' Q ')
[email protected]> select sysdate from dual;SYSDATE-------------------2015-07-25 06:37:17[email protected]> select to_char(sysdate, ‘Q‘) from dual;T-3
What's the day of the Year (1-366) for today's date? How many days are the current date in the same year?
To_char (sysdate, ' DDD ')
[email protected]> select TO_CHAR (SYSDATE, ‘DDD‘) from dual;TO_---206
What is the date and time of a date variable?
BEGIN DBMS_OUTPUT.put_line ( TO_CHAR (SYSDATE, ‘YYYY-MM-DD HH24:MI:SS‘));END;/
You can also use EXTRACT to EXTRACT and return the value of a specified element of a date. For example
You can also specify element values using the Extract fetch date:
What's it? Current year?
EXTRACT (year from Sysdate)
[email protected]> select EXTRACT (YEAR FROM SYSDATE) from dual;EXTRACT(YEARFROMSYSDATE)------------------------ 2015
What's the day for today ' s date? The number of days in your current month?
EXTRACT (Day from Sysdate)
[email protected]> select EXTRACT (DAY FROM SYSDATE) from dual;EXTRACT(DAYFROMSYSDATE)----------------------- 25
How do I convert a string to a date? Use To_date or To_timestamp built-in functions.
DECLARE l_date DATE;BEGIN l_date := TO_DATE (‘12-JAN-2011‘);END ;
If The string you provide does not match the default format, Oracle Database would raise an exception:
Note: If you provide a string argument that is inconsistent with the format parameter model of the database or session settings, the Oracle database throws an exception:
DECLARE l_date DATE;BEGIN l_date := TO_DATE (‘January 12 2011‘);END;/ORA-01858: a non-numeric character was found where a numeric was expected
You should isn't assume that the literal value of provide in your call to To_date matches the default format. What if the format changes over time? Instead, always provide a format mask when converting strings to dates, as in
We should always specify the format because you cannot determine when the format parameter will change.
L_date: = To_date (' January ', ' Month DD YYYY ');
5. Date Truncation
Use the trunc built-in function to intercept a specified unit of a date. The most common usage is trunc (DATE)-no parameters are specified. At this point, trunc only sets the time portion to 00:00:00.
Set L_date to today's date, but with the time set to 00:00:00:
L_date: = TRUNC (sysdate);
Get the first day of the month of the current date get the specified date:
L_date: = TRUNC (sysdate, ' MM ');
Get the first day of the quarter for the specified date:
L_date: = TRUNC (sysdate, ' Q ');
Gets the first day of the year in which the current date was obtained. The one for the specified date:
L_date: = TRUNC (sysdate, ' Y ');
‘Y‘) from dual;TRUNC(SYSDATE,‘Y‘)-------------------2015-01-01 12:00:00
6. Date arithmetic arithmetic
For operations on date and time stamps, the Oracle database provides the following methods:
ADD a numeric value to or subtract it from a date, as in Sysdate + 7; Oracle Database treats the number as the number of days.
Add or subtract a value for the specified date, for example: Sysdate + 7; Oracle considers the number units to be: days.
ADD one date to or subtract it from another, as in L_hiredate-sysdate.
Two date direct add minus, for example: L_hiredate-sysdate.
Use a built-in function to ' move ' a date by a specified number of months or to another date in a week.
Use the built-in function to move a date by a specified number of months or another date within the week.
Set a local variable to tomorrow ' s date: Sets the date variable to tomorrow
L_date: = sysdate + 1;
Move back one hour: Push forward for 1 hours
L_date: = sysdate-1/24;
Move ahead seconds: Push forward for 10 seconds
L_date: = sysdate + 10/(60 * 60 * 24);
When you add a date to or subtract it from another, the result is the number of days between the. As a result, executing this block:
If you add and subtract 2 dates, the result is the number of days in the 2 date interval.
DECLARE l_date1 DATE := SYSDATE; l_date2 DATE := SYSDATE + 10;BEGIN DBMS_OUTPUT.put_line ( l_date2 - l_date1); DBMS_OUTPUT.put_line ( l_date1 - l_date2);END;returns the following output:10-10
And the following function can be used to compute the age of a person, assuming that the person's correct birth date is PA Ssed as the function ' s only argument:
In addition, the following functions can be used to calculate a person's age:
CREATE OR REPLACE FUNCTION your_age (birthdate_in IN DATE) RETURN NUMBERISBEGIN RETURN SYSDATE - birthdate_in;END your_age;
Here are a few of the built-in functions:
add_months-the specified number of months for a date or time stamp
Next_day-the next week of current system time? Date of the next one weeks (specified by char) for the specified time
The date parameter is a datetime type,
Char: for 1~7 or Monday~sunday
The date of the next one weeks (specified by char) for the specified time,
Char can also be substituted with 1~7, 1 means Sunday, 2 for Monday ....
It can also be Monday, Tuesday ... Sunday
last_day-returns the date of the last day of the month on which the specified date
Move ahead one month: Push backwards for 1 months "translator Note: one month later"
L_date: = Add_months (sysdate, 1);
Move backward three months: Push forward 3 months "translator note: one month ago"
L_date: = Add_months (Sysdate,-3);
SCOTT@orcl> Select Sysdate, 2last_day (sysdate) "Last", 3last_day (sysdate) -Sysdate ' days Left '4 from DUAL ;sysdate Last Days Left------------------- ------------------- ----------2015-07-2507: Geneva:2015-07-3107: Geneva:6
Code Listing 3:calls to Add_months
begin dbms_output.put_ Line (add_months to_date (, ' dd-mon-yyyy ' ), 1 ); Dbms_output.put_line (Add_months (to_date ( ' 27-feb-2011 ' , ' dd-mon-yyyy ' ),-1 ); Dbms_output.put_line (Add_months (to_date ( ' 28-feb-2011 ' , ' dd-mon-yyyy ' ),-1 ); end ; Here is the output: 28 -feb- 11 27 -jan-11 31 -jan-11
Find the next Saturday after today's date:
Find the next Saturday after today!
L_date: = Next_day (sysdate, ' SAT ');
L_date: = Next_day (sysdate, ' SATURDAY ');
Copyright NOTICE: This article for Bo Master original article, without Bo Master permission not reproduced.
Using date types in PL/SQL
Start building with 50+ products and up to 12 months usage for Elastic Compute Service