Shulou(Shulou.com)06/01 Report--

1, SYSDATE gets the current system time

select SYSDATE from dual;

Format Date: TO_CHAR(SYSDATE,'YY/MM/DD HH24:MI:SS)

OR TO_DATE(SYSDATE,'YY/MM/DD HH24:MI:SS)

Format of conversion:

Y represents the last digit of the year.

yy represents the last 2 digits of the year

yyy represents the last 3 digits of the year

yy uses 4 digits to represent the year

Month: mm 2-digit month

mon is abbreviated, such as November or nov

month is used in full, such as November or November

dd indicates the day of the month

ddd indicates the day of the year

dy Day of the week, abbreviated as Friday or fri

Day is the day of the week, such as Friday or Friday.

hour: hh 2 digits for hour decimal

hh34 2-digit number for hour 24 hours

2-digit number for minute

The second: ss 2 digits indicate the second 60 base

Q: A single digit indicates a quarter (1-4)

select to_char(sysdate,'q') from dual;

select to_char(sysdate,'Q') from dual;

Ww is also used to indicate the week of the year w is used to indicate the week of the month.

Time range in 24 hours: 00:00-23:59:59

Time range in 12 hour clock: 1:00:00-12:59:59

ADD_MONTHS(d,) increments the given date by month

select sysdate,add_months(sysdate,12) from dual; --plus 1 year

select sysdate,add_months(sysdate,1) from dual; --plus 1 month

select sysdate,add_months(sysdate,-12) from dual; --minus 1 year

select sysdate,add_months(sysdate,-1) from dual; --minus 1 month

LAST_DAY(d) returns the last day of the specified month.

select last_day(sysdate) from dual;

MONTHS_BETWEEN(d1,d2) Returns the month between two dates

select months_between(sysdate,to_date('2017/12/31','yyyy/mm/dd')) from dual;

5, NEW_TIME(d,tz1,tz2)

d is a data type that returns the date and time in time zone tz2 when the date and time of tz1 is d, both tz1 and tz2 are strings.

Time zone 1 Time zone 2 Description

AST ADT Atlantic Standard Time

BST BDT Bering Sea Standard Time

CST CDT Central Standard Time

EST EDT Eastern Standard Time

GMT Greenwich Mean Time

HST HDT Alaska-Hawaii Standard Time

MST MDT Mountain Standard Time

NST Newfoundland Standard Time

PST PDT Pacific Standard Time

YST YDT YUKON Standard Time

select to_char(sysdate,'yy/mm/dd hh34:mi:ss') Beijing

to_char(new_time(sysdate,'PDT','GMT'),'yy/mm/dd hh34:mi:ss') Los Angeles from dual;

6, NEXT_DAY(d,string)

Calculates the first day after date d that satisfies the condition given by string.String Use position; the language of the current session specifies a day of the week.

The time component of the return value is the same as the time component of d. The contents of String can ignore case.

select next_day ('17-Sep-2012','Saturday') next_day from dual;

7, ROUND(d[,format])

Process date d in the format specified by format. If no format is given then the default setting 'DD' is used.

8, TRUNC(d,format)

Computes the date truncated to the units specified by format d. Available with Position: Format and Effect. Default parameters are the same as ROUNG.

trunc(date,fmt) truncates the date as given, if fmt='mi' means minutes reserved, seconds truncated

select to_char(trunc(sysdate,'hh'),'yyyy/mm/dd hh34:mi:ss') HH,

to_char(trunc(sysdate,'mi'),'yyyy/mm/dd hh34:mi:ss') HHMM,

to_char(trunc(sysdate,'dd'),'yyyy/mm/dd hh34:mi:ss') DD,

to_char(trunc(sysdate,'mm'),'yyyy/mm/dd hh34:mi:ss') MM from dual;

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.