Common time and date functions in oracle

Date functions will be used in almost all databases. Next I will introduce some detailed instructions on the use of Sybase date functions. For more information, see.

-- Date-related functions
-- 1.day( date_time) returns an integer that represents the day in datepart of the specified date. Month (date_time), year (date_time) is similar
Select day (getdate () -- returns 23
-- 2. datepart (datepart, date), return the part of the datetime, smalldatetime, date, or time value (for example, month) that is expressed as an integer.
SELECT datepart (yy, getdate () -- returns the year 2010 of the date component.
SELECT datepart (MM, getdate () -- returns the date component month 3
SELECT datepart (dd, getdate () -- returns the date component day 23
SELECT datepart (hh, getdate () -- returns the date weight of 11
SELECT datepart (mi, getdate () -- returns the date component minute 10
SELECT datepart (ss, getdate () -- returns the date component seconds 55
SELECT datepart (MS, getdate () -- returns the date component in milliseconds 955
SELECT datepart (wd, getdate () -- returns the date Component 3 of the week, counted from Sunday.
SELECT datepart (dy, getdate () -- returns the day 82 of the date component year, counted from Sunday.
-- 3. dateadd (datepart, number, date) returns the date generated by adding the date component to another date
SELECT dateadd (dd, 5, getdate ())
-- Add the date component of the current time to the daily component by 5 days (if you want to subtract 5 days, it is written as-5, and the other date components are similar), and return 2010-03-28 11: 10: 55: 955
-- 4. datename (datepart, date), returns the part of the datetime, smalldatetime, date, or time type, represented by an ASCII string.
SELECT datename (dw, getdate () -- returns Tuesday
-- 5. datediff (datepart, startdate, enddate), returns the difference of the date component where enddate-startdate is located. startdate-enddate is a negative value.
SELECT datediff (hh, '2017-03-23 11: 10: 55: 66661', '2017-03-23 12: 05: 55: 66661 ')
-- 0 is returned, because there is no difference of one hour.

-- Obtain the beginning of the year from the current time
Select convert (datetime, CONVERT (VARCHAR (4), datepart (yy, getdate () + '2014 00:00:00 ')
-- Obtain the beginning of the month from the current time
Select convert (datetime, CONVERT (VARCHAR (12), dateadd (dd,-datepart (dd, getdate () + 1, getdate (), 112) + '00:00:00 ')
Select convert (datetime, CONVERT (VARCHAR (12), dateadd (dd,-DAY (getdate () + 1, getdate (), 112) + '00:00:00 ')
 
-- Obtain the time range of the last week from the current time. For example, if the current time is 20100323 11:10:55, the value is 20100315 00:00:00 to 20100322 00:00:00, and the default value is 0.
Select convert (datetime, CONVERT (VARCHAR (12), dateadd (dd,-(datepart (dw, getdate () + 5), getdate (), 112 ))
Select convert (datetime, CONVERT (VARCHAR (12), dateadd (dd,-(datepart (dw, getdate ()-2), getdate (), 112 ))

-- The date format is 20102303.
Select convert (VARCHAR (24), getdate (), 112)

-- Get the Date Format: 2010/23/03
Select convert (VARCHAR (24), getdate (), 113)

-- The date format is Mar 23 2010 11: 11: 18: 346.
Select convert (VARCHAR (24), getdate (), 109)

Add an article

Common date functions

1. Sysdate current date and time SQL> Select sysdate from dual;

 

2. SQL> Select Last_day (sysdate) from dual;

 

3. Add_months (d, n) the current date d is followed by n months to increase or decrease from a date value date_value: = add_months (date_value, number_of_months)

SQL> Select add_months (sysdate, 2) from dual;

 

4. Months_between (f, s) date, number of different months between f and s

SQL> select months_between (sysdate, to_date ('2017-11-12 ', 'yyyy-mm-dd') from dual;

 

5. NEXT_DAY (d, day_of_week) returns the date of the nth workday after the date specified by the variable "d", named after "day_of_week. The parameter "day_of_week" must be one day of the week.

SELECT next_day (to_date ('20140901', 'yyyymmdd'), 1) FROM dual;

Select next_day (sysdate, 3) from dual; indicates the third working day, and the first foreign job is Sunday. NOTE: If today is Monday, it indicates the Tuesday of this week. If this week has passed, it indicates the Tuesday of next week.

 

6. Current_date () returns the current date date_value: = current_date SQL> column sessiontimezone for a15 SQL> select sessiontimezone, current_date from dual;

SQL> alter session set time_zone = '-' 2/the session has been changed. SQL> select sessiontimezone, current_timestamp from dual;

 

7. Current_timestamp () returns the current date in the current session time zone as the timestamp with time zone Data Type

SQL> select current_timestamp from dual;

 

8. Dbtimezone () returns the time zone SQL> select dbtimezone from dual;

 

9. 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;

SQL> select extract (year from add_months (sysdate, 36) "Years" from dual;

Years ---------- 2008

 

10. Localtimestamp () returns the date and time SQL> select localtimestamp from dual;

LOCALTIMESTAMP ---------- 21-6-05 10.18.15.855652 AM

 

11. TRUNC (for dates)
The date value intercepted by the TRUNC function for the specified element.
The syntax format is as follows:
TRUNC (date [, fmt])
Where:
Date: A date value.
Fmt date format, which is truncated by the specified Element format. Ignore it and it is intercepted by the latest date.
The usage of this function is as follows:
1) end by year
Select TRUNC (TO_DATE ('2017-03-01 ', 'yyyy-mm-dd hh: mi'), 'yyyy') from dual
-----------------------------------------------------------
2008-1-1-1
2) Cut the end of each month
Select TRUNC (TO_DATE ('2017-03-01 ', 'yyyy-mm-dd hh: mi'), 'mm') from dual
--------------------------------------------------------
2008-3-1
3) Cut the end by day
Select TRUNC (TO_DATE ('2017-03-01 ', 'yyyy-mm-dd hh: mi'), 'dd') from dual
----------------------------------------------------------------------
2008-3-1
4) Cut the end on time
Select TRUNC (TO_DATE ('2017-03-01 ', 'yyyy-mm-dd hh: mi'), 'hh') from dual
----------------------------------------------------------------------
2008-3-1 8:00:00
5) Cut the end by minute
Select TRUNC (TO_DATE ('1970-03-01 ', 'yyyy-mm-dd hh: mi'), 'mi') from dual
----------------------------------------------------------------------
2008-3-1 8:23:00