Use the date type in PL/SQL

Source: Internet
Author: User
Tags time zones

Use the date type in PL/SQL

Use the date type in PL/SQL

Previous articles introduced the use of strings and numbers in PL/SQL. There is no doubt that strings and numbers are important, but it can be determined that no application depends on the date.

Oracle uses a string-indexed two-dimensional array

You need to record events, people's birth dates, and so on.

The common usage is as follows:

1) Declare date variables and constants

2) use built-in functions to display and modify date values

3) date calculation

The date type is more complex than the string or number. It consists of multiple parts (year, month, day, hour, minute, second, etc.), and there are many rules for a valid date.
This article will give you all the information to use dates in PL/SQL programs.

1. Date, timestamp, and interval (Intervals) in PL/SQL)

Most applications need to store and operate on the date and time. Unlike strings and numbers, date requirements are more complex: not only is it because they are more advanced in formatting data, but they both have valid values and effective calculation methods.
Many rules.

Fortunately, Oracle Database and PL/SQL provide a set of true date and time datatypes that store both date and time information in a standard internal format, and they also have an extensive set of built-in functions for manipulating the date and time.

Fortunately, Oracle databases and PL/SQL provide a series of Date and Time types to store date and time information in the standard form, and a series of built-in functions for date and time computing.
There are three related types:
1) DATE-This datatype stores a date and a time, resolved to the second. it does not include the time zone [time zone ]. DATE is the oldest and most commonly used datatype for working with dates in Oracle applications. it is also the oldest and most commonly used type.

2) TIMESTAMP-Time stamps are similar to dates, but with these two key distinctions:
(2.1) you can store and manipulate times resolved to the nearest billionth of a second (9 decimal places of precision), and
The storage accuracy reaches 1 billion points per second.
(2.2) you can associate a time zone with a time stamp, and Oracle Database will take that time zone into account when manipulating the time stamp. time zone is supported.

3) INTERVAL-Whereas DATE and TIMESTAMP record a specific point in time, INTERVAL records and computes a time duration. You can specify an interval in terms of years and months, or days and seconds.
Record interval. You can specify the time interval between the year, month, and second.

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   TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;   l_interval1         INTERVAL YEAR (4) TO MONTH := '2011-11';   l_interval2         INTERVAL DAY (2) TO SECOND := '15 00:30:44';BEGIN   null;END;/

2. How do I select the appropriate date type?
1) Use one of the TIMESTAMP types if you need to track time down to a fraction of a second.

2) You can, in general, use TIMESTAMP in place of DATE. A time stamp that 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 11 bytes of storage.

3) Use timestamp with time zone if you need to keep track of the session time zone in which the data was entered.

4) Use timestamp with local time zone if you want the database to automatically convert a time between the database and session time zones. automatically converts the database TIME and session time time zone.

5) Use DATE when it's necessary to maintain compatibility with an existing application written before any of the TIMESTAMP datatypes were introduced. Forward compatibility is maintained.

3. How to obtain the current time?
I believe that most developers use the SYSDATE function, but the Oracle database also provides some other functions. Let's take a look:

1) Session-level functions:
CURRENT_DATE: DATE
CURRENT_TIMESTAMP: TIMESTAMP WITH TIME ZONE
LOCALTIMESTAMP: TIMESTAMP

2) database level
SYSDATE: DATE
SYSTIMESTAMP returned: TIMESTAMP WITH TIME ZONE

Code Listing 2: callto SYSDATE and policimestamp and the returned values

For example:

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
07-AUG-11 08.46.16.0000000000 AM-05:00
-000000000 00:00:00. 379000000

Because I pass the date and time stamp to DBMS_OUTPUT.PUT_LINE, the Oracle database implicitly converts it to a string using the default format (parameter: NLS_DATE_FORMAT) at the database or session level.
The default format for database installation is DD-MON-YYYY. The default timestamp format includes the offset of the date and time zone ).

How to modify it? [Note]
1). You can specify (LINUX) in user environment variables ). Add two sentences to your. 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 the following statement to the SQLPLUS glogin. SQL file: alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
3) directly modify the date format of the current session: alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
4) to modify the database parameters, restart the database to take effect. SQL> alter system set nls_date_format = 'yyyy-mm-dd hh24: mi: ss' scope = spfile;

4. How to convert date to string and string to date?

Just like the to_char function for numbers, we use another version of to_char to convert the date or timestamp type to the string.

If to_char with no format parameter is used. The database uses 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 is determined by the NLS_DATE_LANGUAGE setting, which can also be specified as the third argument in the call to 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:
As follows:

BEGIN  DBMS_OUTPUT.put_line (    TO_CHAR (SYSDATE, 'Day, DDth Month YYYY', 'NLS_DATE_LANGUAGE=Spanish'));END;/Domingo  , 07TH Agosto     2011

In addition, to remove unnecessary 0 and spaces in the display result, I can use the FM element modifier.

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 about, the date, as shown in the following examples:
You can also extract only part of the date using format parameters:
What quarter is it? Next time of the current time?

TO_CHAR (SYSDATE, 'q ')

SCOTT@orcl> select sysdate from dual;SYSDATE-------------------2015-07-25 06:37:17SCOTT@orcl> select to_char(sysdate, 'Q') from dual;T-3

What is the day of the year (1-366) for today's date? The day of the year where the current date is located?

TO_CHAR (SYSDATE, 'ddd ')

SCOTT@orcl> select TO_CHAR (SYSDATE, 'DDD') from dual;TO_---206

What are 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 use EXTRACT to EXTRACT the specified date element value:

What year is it? Current year?

EXTRACT (year from sysdate)

SCOTT@orcl> select EXTRACT (YEAR FROM SYSDATE) from dual;EXTRACT(YEARFROMSYSDATE)------------------------                    2015 

 

What is the day for today's date? The number of days in the current month?

EXTRACT (day from sysdate)

SCOTT@orcl> select EXTRACT (DAY FROM SYSDATE)  from dual;EXTRACT(DAYFROMSYSDATE)-----------------------                     25

How do I convert a string to a date? Use the to_date or to_timestamp built-in function.

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 will raise an exception:
Note: If the string parameters you provide are inconsistent with the format parameter model set by the database or session, 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 shoshould not assume that the literal value you 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.

For example:
Rochelle Date: = TO_DATE ('january 12 100', '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 parameter is specified. At this time, TRUNC only sets the time part to 00:00:00.
For example:

Set l_date to today's date, but with the time set to 00: 00: 00:

Rochelle Date: = TRUNC (SYSDATE );

Get the first day of the month where the current date is located Get the first day of the month for the specified date:

Rochelle Date: = TRUNC (SYSDATE, 'mm ');

Get the first day of the quarter for the specified date:

Rochelle Date: = TRUNC (SYSDATE, 'q ');

Get the first day of the year where the current date is located Get the first day of the year for the specified date:

Rochelle Date: = TRUNC (SYSDATE, 'y ');

SCOTT@orcl> select TRUNC (SYSDATE, 'Y') from dual;TRUNC(SYSDATE,'Y')-------------------2015-01-01 12:00:00

6. Date arithmetic

Oracle Database provides the following methods for date and timestamp operations:

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 to the specified date, for example, SYSDATE + 7. Oracle considers the number to be in the unit of day.

Add one date to or subtract it from another, as in l_hiredate-SYSDATE.
The two dates are directly added or subtracted, 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 to a specified number of months or to another date within the week.

For example:
Set a local variable to tomorrow's date: Set the date variable to tomorrow

Rochelle Date: = SYSDATE + 1;

Move back one hour: 1 hour forward
Rochelle Date: = SYSDATE-1/24;

Move ahead 10 seconds: Push forward for 10 seconds
Rochelle Date: = SYSDATE + 10/(60*60*24 );

When you add one date to or subtract it from another, the result is the number of days between the two. As a result, executing this block:
If you add or subtract two dates, the result is the number of days between two dates.

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 passed 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;

The following describes several built-in functions:
ADD_MONTHS-add or subtract the specified number of months from the date or Timestamp

NEXT_DAY-next week of the current system time? Specifies the date of the next day of the week (specified by char ).
NEXT_DAY (date, char)
The date parameter is of the date type,
Char: 1 ~ 7 or Monday ~ Sunday

Specifies the date of the next day of the week (specified by char,
Char can also be 1 ~ Replace 7. 1 indicates Sunday, and 2 indicates Monday ....
It can also be Monday or Tuesday... Sunday

LAST_DAY-returns the date of the last day of the specified month.

Move ahead one month: one month later]

Rochelle Date: = ADD_MONTHS (SYSDATE, 1 );

Move backward three months: Push forward for 3 months [Note: one month ago]

Rochelle Date: = ADD_MONTHS (SYSDATE,-3 );

SCOTT@orcl> SELECT SYSDATE,  2   LAST_DAY(SYSDATE) "Last",  3   LAST_DAY(SYSDATE) - SYSDATE "Days Left"  4   FROM DUAL;SYSDATE             Last                 Days Left------------------- ------------------- ----------2015-07-25 07:04:17 2015-07-31 07:04:17          6

Code Listing 3: callto ADD_MONTHS

BEGIN   DBMS_OUTPUT.put_line (      ADD_MONTHS (TO_DATE ('31-jan-2011', '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-1127-JAN-11 31-JAN-11 

Find the next Saturday after today's date:
Find the next Saturday after today!

Rochelle Date: = NEXT_DAY (SYSDATE, 'sat ');
-Or
Rochelle Date: = NEXT_DAY (SYSDATE, 'saturday ');

Basic Design of PL/SQL programs for Oracle databases

PL/SQL Developer Practical Skills

Use PL/Scope to analyze PL/SQL code

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.