Oracle date functions

Oracle date functions

Oracle date functions

The ORACLE tutorial is: oracle date function.

There are many date functions in oracle,

For example:

1. add_months () is used to increase or decrease the date_value: = add_months (date_value, number_of_months) of some months from a date value. Example: SQL> select add_months (sysdate, 12) Next Year from dual; next Year ---------- month 13-11-04 SQL> select add_months (sysdate, 112) Last Year from dual; Last Year ---------- 13-3-13 SQL>

2. current_date () returns the current date date_value: = current_date SQL> column sessiontimezone for a15 SQL> select sessiontimezone, current_date from dual; SESSIONTIMEZONE CURRENT_DA --------------- ---------- + 08:00 13-11-03 SQL> alter session set time_zone =-11: 00 2/the session has been changed. SQL> select sessiontimezone, current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------03 04.59.13.668000 pm-11: 00 SQL>

3. current_timestamp () returns the current date timestamp_with_time_zone_value: = current_timestamp ([timestamp_precision]) from the timestamp with time zone data type. SQL> column sessiontimezone for a15 SQL> column current_timestamp format a36 SQL> select sessiontimezone, current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP ------------- hour +-Am + 08: 0 0 SQL> alter session set time_zone =-11: 00 2/the session has been changed. SQL> select sessiontimezone, current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP --------------03 04.58.00.243000 pm-11: 00 SQL>

4. dbtimezone () returns the time zone varchar_value: = dbtimezone SQL> select dbtimezone from dual; DBTIME -------07:00 SQL>

5. extract () identifies the field value of the date or interval value date_value: = extract (date_field from [datetime_value | interval_value]) SQL> select extract (month from sysdate) This Month from dual; this Month ---------- 11 SQL> select extract (year from add_months (sysdate, 36) 3 Years Out from dual; 3 Years Out ----------- 2006

[1]

The ORACLE tutorial is: oracle date function .; SQL>

6. last_day () returns the date date_value: = last_day (date_value) SQL> select last_day (date2000-02-01) Leap Yr? From dual; Leap Yr? ---------- 29-2-00 SQL> select last_day (sysdate) Last day of this month from dual; Last day o ---------- 30-11 month-03 SQL>

7. localtimestamp () returns the date and time timestamp_value: = localtimestamp SQL> column localtimestamp format a28 SQL> select localtimestamp from dual; LOCALTIMESTAMP-03 12.09.15.433000 pm SQL> select localtimestamp, current_timestamp from dual; LOCALTIMESTAMP CURRENT_TIMESTAMP between-03 12.09.31.006000 13-11-03 12.09.31.006000 PM + 08: 00 pm SQL> alter session set time_zone =-11: 00; the session has been changed. SQL> select localtimestamp, to_char (sysdate, DD-MM-YYYY HH: MI: ss am) SYSDATE from dual; localtimestamp sysdate hour ------------------------ 12-11-03 05.11.31.259000 13-11-12:11:31 pm SQL>

8. months_between () determines the number of months between two dates number_value: = months_between (date_value, date_value) SQL> select months_between (sysdate, date1971-05-18) from dual; MONTHS_BETWEEN (SYSDATE, DATE1971-05-18) explain 389.855143 SQL> select months_between (sysdate, date2001-01-01) from dual; MONTHS_BETWEEN (SYSDATE, DATE2001-01-01) ------------------------------------------ 34.4035409 SQL>

9. Given a date value, next_day () returns the date value that appears for the first time on the day indicated by the second parameter (the name string of the day should be returned). This article references oracle China Alliance

[2]