Date types and related functions in Oracle

Oracle
Date type and related functions in

Oracle
Provides many built-in date types, including the following:
Date
Timestamp
Timestamp with Time Zone
Timestamp with local time zone
Interval year to month
Interval day to second

The date type is used to store the date, accurate to seconds. You may set the nls_date_format or to_char method to format the date output.

Timestamp is used to have date and time information. This type is more accurate than date. The minimum value is 9 digits after the decimal point. The following is an example:

Scott @ orcl> select nowdate, nowtimestamp from testtime;

Nowdate nowtimestamp
----------------------------------------
24-nov-08 24-nov-08 10.36.59.000000000 pm

The timestamp with time zone type is very similar to timestamp, but is displayed according to the specified time zone.

The timestamp with local time zone type is more similar. The time is displayed based on the current time zone.
The following is an example:

Scott @ orcl> select * From testtime;

Nowdate nowtimestamp timewithzone timewithlocalzone
-------------------------------------------------------------------------------
24-nov-08 24-nov-08 10.44.55.000000000 PM 24-nov-08 10.44.55.000000 PM + 08:00 24-nov-08 10.44.55.000000 pm

You can use alter session set to set the timestamp output, for example:

Scott @ orcl> alter session set nls_date_format =
'Yyyy-MM-DD hh24: MI: ss ';
Session altered.
Scott @ orcl> alter session set nls_timestamp_format
= 'Yyyy-MM-DD hh24: MI: Ss. ff ';
Session altered.
Scott @ orcl> alter session set nls_timestamp_tz_format
= 'Yyyy-MM-DD hh24: MI: Ss. fftzh: tzm ';
Session altered.

Scott @ orcl>/

Nowdate nowtimestamp timewithzone timewithlocalzone
------------------------------------------------------------------------------------------------------------
22:44:55 22:44:55. 000000000 22:44:55. 000000 + 08: 00 22:44:55. 000000

The interval year [(precision)] to month type is used to represent a period of time in the unit of year and month. It is mainly used to express the interval between two times, such as one year and three months. In year, you can set an accuracy value ranging from 0 to 9. The default value is 2.

Interval day [(precision)] to second is similar to the preceding type. It is mainly used to indicate a period of time in the unit of day, hour, minute, and second. You can set a precision on day. The value ranges from 0 to 9, and the default value is 6.
The two interval types can be used for addition, subtraction, multiplication, and Division operations. The following two examples show how these two types work:
Scott @ orcl> Create Table test_interval (ts1 timestamp (2), y2m interval year (4) to month, D2S interval day (4) to second );

Table created

Scott @ orcl> insert into test_interval values (effecimestamp, to_yminterval ('4-7'), to_dsinterval ('5 02:20:30. 30 ));

Scott @ orcl> select * From test_interval;

Ts1 y2m D2S
-----------------------------------------------------------------
23:06:10. 71 + 0004-07 + 0005 02:20:30. 300000

The preceding example shows that the to_yminterval () and to_dsinterval () methods can convert a string to the interval type.
The following example shows how to use this type:

Scott @ orcl> select sysdate
, Sysdate
+ T. y2m, sysdate
+ T. D2S from test_interval T;

Sysdate
Sysdate
+ T. y2m sysdate
+ T. D2S
---------------------------------------------------------
23:25:02 23:25:02

As shown in the preceding query, sysdate
+ T. y2m added four years and seven months in that time, sysdate
+ T. D2S added 5 days, 2 hours, 20 minutes, and 30 seconds at that time.

Let's take a look at Oracle
Provided date-related functions
Trunc (date [, FMT]) is used to intercept a date, and its precision is controlled by FMT.
To_timestamp (var1, FMT) converts a string to the timestamp type
To_date (var1, FMT) converts string to date type
Add_months (date, n) is increased by N months. If it is negative, it is decreased by N months.
Last_day (date) the last day of the month. You can use add_months to get the last day of the other month.
Month_between (date, date) returns the number of months related to two times.
Next_day (date, Dow) Dow indicates the day of the week, and returns the date of the next Dow of date.
Round (date [, FMT]) is similar to the round of data. FMT indicates the accuracy of round. The default value is day, such as hh24 and DD.
Extract (VAR from date) extracts information such as year, month, day, hour, minute, and second from date.

The following are some examples:
SQL> select sysdate
, Add_months (sysdate
, + 1) next_month, add_months (sysdate
,-1) pro_month from dual

Sysdate
Next_month pro_month
------------------------------------------------------------
25-nov-2008 22:29:35 25-dec-2008 22:29:35 25-oct-2008 22:29:35

SQL> select sysdate
,
2 extract (year from sysdate
) Year,
3 extract (month from sysdate
) Month,
4 extract (day from sysdate
) Day
5 * from dual

Sysdate
Year Month day
--------------------------------------------------
25-nov-2008 22:32:01 2008 11 25

 

SQL> select last_day (sysdate
) Last_day from dual;

Last_day
---------
30-nov-08

SQL> select last_day (add_months (sysdate
, 1) next_last_day from dual

Next_last_day
--------------------
31-dec-2008 22:46:25

 

SQL> select sysdate
, Round (sysdate
, 'Hh24') from dual;

Sysdate
Round (sysdate
, 'Hh24'
----------------------------------------
25-nov-2008 22:47:23 25-nov-2008 23:00:00