Using date types in PL/SQL

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
Many rules.

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

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.379000000 am-05:00
-000000000 00:00:00.379000000

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:
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, 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.

For example:
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.
For example:

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.

For example:
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
Next_day (Date,char)
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 ');
–or
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