Use of time and date functions in Oracle

--Date related functions
--1.day (Date_time), returns an integer that represents the day in the datepart of the specified date. Month (date_time), year (date_time) similar
SELECT Day (GETDATE ())--returns 23
--2.datepart (Datepart,date) that returns the part of a type datetime, smalldatetime, date, or time value (for example, a month) expressed as an integer
SELECT datepart (yy,getdate ())--Return date component year 2010
SELECT datepart (mm,getdate ())--Returns the date component month 3
SELECT datepart (dd,getdate ())--Return date component Day 23
SELECT datepart (hh,getdate ())--Returns the date component 11
SELECT datepart (mi,getdate ())--Returns the date component minutes 10
SELECT datepart (ss,getdate ())--Returns the date component seconds 55
SELECT datepart (ms,getdate ())--Returns the date component millisecond 955
SELECT datepart (wd,getdate ())--Returns the date Component Week 3, starting from Sunday
SELECT datepart (dy,getdate ())--Returns the date component the first day of the year 82, starting from Sunday
--3.dateadd (datepart,number,date), which returns the date generated by adding a date component to another date
SELECT DateAdd (Dd,5,getdate ())
--The date component of the current time--the day component adds 5 days (to minus 5 days is written as-5, other date components are similar), returns 2010-03-28 11:10:55:955
--4.datename (datepart,date), which returns the portion of a type datetime, smalldatetime, date, or time value expressed in an ASCII string
SELECT Datename (Dw,getdate ())--Return to Tuesday
--5.datediff (Datepart,startdate,enddate), returns the value of the difference in the date component of the enddate-startdate, Startdate-enddate negative
SELECT DateDiff (hh, ' 2010-03-23 11:10:55:955 ', ' 2010-03-23 12:05:55:955 ')
--return 0, because it's not a one-hour difference.

--from the current time to the beginning of the year
SELECT CONVERT (Datetime,convert (VARCHAR (4), DatePart (Yy,getdate ())) + ' 0101 00:00:00 ')
--At the beginning of the month from the current time
SELECT CONVERT (Datetime,convert (VARCHAR), DateAdd (Dd,-datepart (Dd,getdate ()) +1,getdate ()), 112) + ' 00:00:00 ')
SELECT CONVERT (Datetime,convert (VARCHAR), DateAdd (Dd,-day (getdate ()) +1,getdate ()), 112) + ' 00:00:00 ')

--from the current time to get last week time period, such as the current time 20100323 11:10:55, will be 20100315 00:00:00 to 20100322 00:00:00, when the default is 0
SELECT CONVERT (Datetime,convert (VARCHAR), DateAdd (dd,-(DatePart (dw,getdate ()) +5), GETDATE ()), 112)
SELECT CONVERT (Datetime,convert (VARCHAR), DateAdd (dd,-(DatePart (dw,getdate ())-2), GETDATE ()), 112)

--Get date format of 20102303
SELECT CONVERT (VARCHAR), GETDATE (), 112)

--Get date format as 2010/23/03
SELECT CONVERT (VARCHAR), GETDATE (), 113)

--Get date format for 23 2010 11:11:18:346
SELECT CONVERT (VARCHAR), GETDATE (), 109)

Add an article

Common Date-type functions

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

2. Last_day () The last day of the month sql> Select last_day (sysdate) from dual;

3. Add_months (d,n) Current date D and push n months to increase or decrease some months date_value:=add_months (date_value,number_of_months) from a date value

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

4. Months_between (f,s) Date F and S difference between the number of months

Sql> Select Months_between (sysdate,to_date (' 2005-11-12 ', ' YYYY-MM-DD ') from dual;

5. Next_day (d, Day_of_week) returns the date of the first few working days after the date specified by the variable "D", Named by "Day_of_week". The parameter "Day_of_week" must be one day of the week.

SELECT Next_day (to_date (' 20050620 ', ' YYYYMMDD '), 1) from dual;

Select Next_day (sysdate,3) from dual; for the third working day, the first foreign job for Sunday. Note that if today is Monday, this week's Tuesday, if the Tuesday has passed, then next week's Tuesday

6. Current_date () returns the current date in the current session time zone date_value:=current_date sql> column sessiontimezone for A15 sql> Select Sessiontimezone,current_date from dual;

Sql> alter session set Time_zone= ' -11:00 ' 2/conversation has changed. Sql> select Sessiontimezone,current_timestamp from dual;

7. Current_timestamp () returns the current date in the current session time zone by timestamp with the zone data type

Sql> select Current_timestamp from dual;

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

9. Extract () To find the field value of a 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 of Add_months (sysdate,36)) "Years" from dual;

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

10. Localtimestamp () returns the date and time in the session sql> select Localtimestamp from dual;

Localtimestamp----------2 January-June-05 10.18.15.855652 a.m.

11.TRUNC (for dates)
The date value that the Trunc function truncates for the specified element.
Its specific syntax format is as follows:
TRUNC (Date[,fmt])
which
Date value
FMT the date format, which is truncated by the specified element format. Ignoring it is truncated by the nearest date.
The following is the use of this function:
1) censored by year
Select TRUNC (to_date (' 2008-03-01 08:23 ', ' yyyy-mm-dd hh:mi '), ' yyyy ') from dual
-----------------------------------------------------------
2008-1-1
2) cut by the end of the month
Select TRUNC (to_date (' 2008-03-01 08:23 ', ' yyyy-mm-dd hh:mi '), ' mm ') from dual
--------------------------------------------------------
2008-3-1
3) By the end of the day
Select TRUNC (to_date (' 2008-03-01 08:23 ', ' yyyy-mm-dd hh:mi '), ' DD ') from dual
----------------------------------------------------------------------
2008-3-1
4. Cut the tail on time
Select TRUNC (to_date (' 2008-03-01 08:23 ', ' yyyy-mm-dd hh:mi '), ' HH ') from dual
----------------------------------------------------------------------
2008-3-1 8:00:00
5) by cutting tail
Select TRUNC (to_date (' 2008-03-01 08:23 ', ' yyyy-mm-dd hh:mi '), ' mi ') from dual
----------------------------------------------------------------------
2008-3-1 8:23:00