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

1. Commonly used date functions:

DAY ()-the function returns the date value in date_expression

MONTH ()-the function returns the month value in date_expression

YEAR ()-the function returns the year value in date_expression

DATEADD (,)

-- the function returns the new date generated by the specified date date plus the specified extra date interval number

DATEDIFF (,)

The function returns the datepart differences between the two specified dates

DATENAME (,)-the function returns the specified part of the date as a string

DATEPART (,)-the function returns the specified part of the date as an integer value.

GETDATE ()-the function returns the current date and time of the system in the default format of DATETIME

TO_DATE format (take time: 2007-11-02 13:45:25 as an example)

Year:

Yy two digits two-digit year display value: 07

Yyy three digits 3-digit year display value: 007

Yyyy four digits four-digit year display value: 2007

Month:

Mm number two-digit month display value: 11

The mon abbreviated character set represents the display value: November, if the English version, display nov

The month spelled out character set represents the display value: November, if the English version, display november

Day:

The day of the month in dd number displays the value: 02

The day of the year in which ddd number displays the value: 02

Dy abbreviated the day of the week abbreviated display value: Friday, if the English version, display fri

Day spelled out full write display value on the day of the week: Friday, if the English version, display friday

Ddspth spelled out, ordinal twelfth

Hour:

Hh two digits 12-hour display value: 01

Hh34 two digits 24-hour display value: 13

Minute:

Mi two digits 60 binary display value: 45

Second:

Ss two digits 60 binary display value: 25

Other

Q digit quarterly display value: 4

WW digit shows a value of 44 in what week of the year

W digit shows the value of the week of the month: 1

Under the 24-hour format, the time range is 0:00:00-23 59.

The time range in the 12-hour format is: 1:00:00-12:59:59.

Second, the specific methods used in the date function:

1. Date and character conversion function usage (to_date,to_char)

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss') as nowTime from dual; / / date converted to a string

Year of select to_char (sysdate,'yyyy') as nowYear from dual; / / acquisition time

Select to_char (sysdate,'mm') as nowMonth from dual; / / month of acquisition time

Select to_char (sysdate,'dd') as nowDay from dual; / / the day on which the time is obtained

Select to_char (sysdate,'hh34') as nowHour from dual; / / when getting the time

Select to_char (sysdate,'mi') as nowMinute from dual; / / minutes of acquisition time

Select to_char (sysdate,'ss') as nowSecond from dual; / / get the seconds of the time

Select to_date ('2004-05-07 13 from dual// 23 Swiss 44 mm hh34:mi:ss') from dual//

two。

Select to_char (to_date (222 Jsp' J') from dual

Show Two Hundred Twenty-Two

3. Ask what day it is one day

Select to_char (to_date ('2002-08-26) from dual

Monday

Select to_char (to_date ('2002-08-26) from dual

Monday

Set date language

ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'

You can do that, too.

TO_DATE ('2002-08-26,' YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')

4. The number of days between two days

Select floor (sysdate-to_date ('20020405)) from dual

5. Time is the use of null

Select id, active_date from table1

UNION

Select 1, TO_DATE (null) from dual

Pay attention to using TO_DATE (null)

6. Monthly difference

A_date between to_date ('200112019) and to_date (' 20011231)

Then after 12:00 on December 31st and before 12:00 on December 1st are not included in this range.

So, when the time needs to be precise, I think to_char is still necessary.

7. Date format conflict problem

The format you enter depends on the type of ORACLE character set you install, for example: US7ASCII, the type of date format is:'01'

Alter system set NLS_DATE_LANGUAGE = American

Alter session set NLS_DATE_LANGUAGE = American

Or write in to_date

Select to_char (to_date ('2002-08-26) from dual

Notice that I'm just raising NLS_DATE_LANGUAGE, and of course there's a lot more.

Viewable

Select * from nls_session_parameters

Select * from V$NLS_PARAMETERS

8.

Select count (*)

From (select rownum-1 rnum

From all_objects

Where rownum select sysdate, to_char (sysdate,'hh') from dual

SYSDATE TO_CHAR (SYSDATE,'HH')

--

2003-10-13 19:35:21 07

SQL > select sysdate, to_char (sysdate,'hh34') from dual

SYSDATE TO_CHAR (SYSDATE,'HH24')

2003-10-13 19:35:21 19

13. The treatment of the year, month and day

Select older_date

Newer_date

Years

Months

Abs (

Trunc (

Newer_date-

Add_months (older_date,years*12+months)

)

) days

From (select

Trunc (months_between (newer_date, older_date) / 12) YEARS

Mod (trunc (months_between (newer_date, older_date)), 12) MONTHS

Newer_date

Older_date

From (

Select hiredate older_date, add_months (hiredate,rownum) + rownum newer_date

From emp

)

)

14. The way to deal with the variable number of days of the month

Select to_char (add_months (last_day (sysdate) + 1,-2), 'yyyymmdd'), last_day (sysdate) from dual

16. Find out the number of days this year

Select add_months (trunc (sysdate,'year'), 12)-trunc (sysdate,'year') from dual

The treatment of leap years

To_char (last_day (to_date ('02' | |: year,'mmyyyy')),' dd')

If it's 28, it's not a leap year.

The difference between 17.yyyy and rrrr

'YYYY99 TO_C

-

Yyyy 99 0099

Rrrr 99 1999

Yyyy 01 0001

Rrrr 01 2001

18. Treatment of different time zones

Select to_char (NEW_TIME (sysdate, 'GMT','EST'),' dd/mm/yyyy hh:mi:ss'), sysdate

From dual

19.5 seconds at an interval

Select TO_DATE (FLOOR (TO_CHAR (sysdate,'SSSSS') / 300) * 300), TO_CHAR (sysdate,'SSSSS')

From dual

2002-11-1 9:55:00 35786

SSSSS represents 5-digit seconds

20. The day of the year

Select TO_CHAR (SYSDATE,'DDD'), sysdate from dual

310 2002-11-6 10:03:51

21. Calculate hours, minutes, seconds, milliseconds

Select

Days

A

TRUNC (Atom 24) Hours

TRUNC (Atom 24060-60*TRUNC (Atom 24)) Minutes

TRUNC (Av2406060-60*TRUNC (A24060)) Seconds

TRUNC (A24 / 60 / 60 / 100-100*TRUNC) mSeconds

From

(

Select

Trunc (sysdate) Days

Sysdate-trunc (sysdate) A

From dual

)

Select * from tabname

Order by decode (mode,'FIFO',1,-1) * to_char (rq,'yyyymmddhh34miss')

/ /

Floor ((date2-date1) / 365) as a year

Floor ((date2-date1, 365) / 30) as month

D (mod (date2-date1, 365,30) as a day.

The 23.next_day function returns the date of next week, day is 1-7 or Sunday-Saturday, 1 means Sunday

Next_day (sysdate,6) is the next Friday from now on. The latter figures are counted from Sunday.

1 2 3 4 5 6 7

One, two, three, four, five, six.

Select (sysdate-to_date ('2003-12-03 12 hh34:mi:ss' 55 hh34:mi:ss') * 24' 60'60 from ddual

The date returns days and then converts to ss

24 day round [rounded to the nearest date] (rounding to the nearest Sunday)

Select sysdate S1

Round (sysdate) S2

Round (sysdate,'year') YEAR

Round (sysdate,'month') MONTH

Round (sysdate,'day') DAY from dual

25herotrunc [truncated to the nearest date, in days], returns the date type

Select sysdate S1

Trunc (sysdate) S2, / / returns the current date with no time, minutes and seconds

Trunc (sysdate,'year') YEAR, / / returns January 1st of the current year, without time and seconds

Trunc (sysdate,'month') MONTH, / / returns the first day of the current month without time, minutes and seconds

Trunc (sysdate,'day') DAY / / returns the Sunday of the current week, without time and seconds

From dual

26, returns the latest date in the date list

Select greatest (October-February-04') from dual

twenty-seven。 Calculate the time difference

Note: the time difference of oracle is measured in days, so it is converted into years, months and days.

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss')) /) as spanYears from dual / / time difference-year

Select ceil (moths_between (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss') as spanMonths from dual / time difference-month

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss') as spanDays from dual / time difference-day

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss')) * 24) as spanHours from dual / / time difference

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss')) * 24060) as spanMinutes from dual / / time difference

Select floor (to_number (sysdate-to_date ('2007-11-02 15 sysdate-to_date 55 sysdate-to_date 03mm hh34:mi:ss') * 24 hours 60 minutes 60) as spanSeconds from dual / / time difference-second

twenty-eight。 Update time

Note: the addition and subtraction of oracle time is in days, and the variable is set to n, so it is converted into year, month and day.

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n*365,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-year

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), add_months (sysdate,n) as newTime from dual / / change time-month

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-day

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n/24,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-hour

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n/24/60,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-min

Select to_char (sysdate,'yyyy-mm-dd hh34:mi:ss'), to_char (sysdate+n/24/60/60,'yyyy-mm-dd hh34:mi:ss') as newTime from dual / / change time-second

twenty-nine。 Find the first and last day of the month

SELECT Trunc (Trunc (SYSDATE, 'MONTH')-1,' MONTH') First_Day_Last_Month

Trunc (SYSDATE, 'MONTH')-1 / 86400 Last_Day_Last_Month

Trunc (SYSDATE, 'MONTH') First_Day_Cur_Month

LAST_DAY (Trunc (SYSDATE, 'MONTH')) + 1-1 / 86400 Last_Day_Cur_Month

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.