1, TO_DATE () Value in the TO_DATE () function Description: Day: DD two-digit format, the maximum 31 DY worship English abbreviation format, such as the worship 5 abbreviation for the English format of Fri Day, for example, FRIDAY DDSPTH Spelled Out, Ordinal TWELTH MONTH: MM Two-digit format, up to 12 MON English-moon abbreviation format, such as March abbreviated as full form of March English month, for example March Year: YY two digital format , For example, 98 represents the 1998 YYYY four-digit format, such as 1998 Represents 1998 HOUR: HH12 indicates 12 hours chronograph HH24 indicates 24 hours timing minute: Mi indicates that the minute second: SS represents the number of seconds, to_date () and TO_CHAR () functions Use select to_char (to_date (222, 'J'), 'JSP') from Dual Display Two Hundred TWENTY-TWO
3, seek a day in the week Select to_Char (to_date "
2002-08-26
',' YYYY-MM-DD '),' Day ') from Dual; Monday Select to_Char (to_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGE = American ') from Dual; Monday Settings Date Language
Alter session set nls_date_ilanguage = 'American';
It can also be
TO_DATE ('
2002-08-26
',' YYYY-MM-DD ',' NLS_DATE_LANGUAGE = American ') 4, the number of days during two days Select floor (sysdate - to_date (' 20020405 ',' YYYMMDD ')) from Dual; 5, time is NULL usage
SELECT ID, ACTIVE_DATE from Table1
Union
SELECT 1, TO_DATE (NULL) from DUAL
Note To use to_date (null)
6, A_DATE BETWEEN TO_DATE ('20011201', 'YYYYMMDD') AND to_DATE ('2001231', 'YYYYMMDD')
Then, 12:00 on December 31, it is not included in this range before 12 o'clock on December 1st.
So, when time needs to be precise, try to_char is still necessary.
7, date format conflict problem
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGE = American ') from Dual; Note I just got NLS_DATE_LANGUAGE, of course there are still many,
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24') ----------------------------------------- -
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS'); //
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SESTEM SET NLS_DATE_LANGUAGE = Americanalter Session Set NLS_DATE_LANGUAGE = AMERICAN
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) MSECONDSFROM
(
SELECT
Trunc (sysdate) Days,
Sysdate - trunc (sysdate) a
From dual
)
SELECT * from TabName
Order by decode (Mode, 'FIFO', 1, -1) * to_CHAR (RQ, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7 Days and Three Four Version
SELECT ID, ACTIVE_DATE from Table1
Union
SELECT 1, TO_DATE (NULL) from DUAL
Note To use to_date (null)
6, A_DATE BETWEEN TO_DATE ('20011201', 'YYYYMMDD') AND to_DATE ('2001231', 'YYYYMMDD')
Then, 12:00 on December 31, it is not included in this range before 12 o'clock on December 1st.
So, when time needs to be precise, try to_char is still necessary.
7, date format conflict problem
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'sssss') / 300) * 300, 'sssss'), to_char (sysdate, 'sssss') from Dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) MSECONDSFROM
(
SELECT
Trunc (sysdate) Days,
Sysdate - trunc (sysdate) a
From dual
)
SELECT * from TabName
Order by decode (Mode, 'FIFO', 1, -1) * to_CHAR (RQ, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for FORMAT CODEMON-Sun, for Format Code DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'est'), 'DD / MM / YYYY HH: MI: SS'), SYSDATEFROM DUAL
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offersql> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours, Trunc (A * 24 * 60 - 60 * Trunc (a * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
Alter session set nls_date_ilanguage = 'American';
It can also be
TO_DATE ('
2002-08-26
',' YYYY-MM-DD ',' NLS_DATE_LANGUAGE = American ') 4, the number of days during two days Select floor (sysdate - to_date (' 20020405 ',' YYYMMDD ')) from Dual; 5, time is NULL usage
SELECT ID, ACTIVE_DATE from Table1
Union
SELECT 1, TO_DATE (NULL) from DUAL
Note To use to_date (null)
6, A_DATE BETWEEN TO_DATE ('20011201', 'YYYYMMDD') AND to_DATE ('2001231', 'YYYYMMDD')
Then, 12:00 on December 31, it is not included in this range before 12 o'clock on December 1st.
So, when time needs to be precise, try to_char is still necessary.
7, date format conflict problem
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ') "MONTHS" from Dual; 1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
To_char (to_day ('02 '||: Year,' mmyyyy ')),' DD ') If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '), TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL Leap Year
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT ID, ACTIVE_DATE from Table1
Union
SELECT 1, TO_DATE (NULL) from DUAL
Note To use to_date (null)
6, A_DATE BETWEEN TO_DATE ('20011201', 'YYYYMMDD') AND to_DATE ('2001231', 'YYYYMMDD')
Then, 12:00 on December 31, it is not included in this range before 12 o'clock on December 1st.
So, when time needs to be precise, try to_char is still necessary.
7, date format conflict problem
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format type is: '01 -jan-01 '
ALTER SYSTEM SET NLS_DATE_LANGUAGE = American
ALTER session set nls_date_language = american or written in To_Date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
Enter format To see the type of Oracle character set you installed, such as US7ASCII, Date format is: '01 -jan-01'alter system set nls_date_language = american
ALTER session set nls_date_language = american
Or written in to_date
SELECT TO_CHAR (To_Date "
2002-08-26
',' YYYY-MM-DD '),' Day ',' NLS_DATE_LANGUAGUE = American ') from DUAL
Note that I just raised NLS_DATE_LANGUAGE, of course there are still many.
View
Select * from nls_session_parameters
Select * from V $ NLS_PARAMETERS
8,
SELECT Count (*)
From (SELECT ROWNUM-1 RNUM
From all_Objects
WHERE ROWNUM <= to_date ('
2002-02-28
',' YYYY-MM-DD ') - TO_DATE (' 2002-
02-01 ',' YYYY-MM-DD ') 1
)
Where to_char (to_date)
2002-02-01
',' YYYY-MM-DD ') RNUM-1,' D ')
NOT
in ('1', '7')
Look up
2002-02-28
to
2002-02-01
Monday and seven days
DBMS_UTILITY.GET_TIME is called before and after, and then the result is subtracted (get 1/100 seconds, not milliseconds) 9,
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds, Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday.
1 2 3 4 5 6 7
Day and two three four five six
SELECT MONTHS_BETWEEN (to_date "
01-31-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1
SELECT MONTHS_BETWEEN (to_date "
02-01-1999
',' Mm-dd-yyyy '),
TO_DATE ('
12-31-1998
',' Mm-dd-yyyy ')) "MONTHS" from DUAL
1.03225806451613
10. Usage of Next_day
Next_day (date, day)
Monday-Sunday, for Format Code Day
MON-Sun, for FORMAT CODE DY
1-7, for Format Code D
11
Select to_char (sysdate, 'hh: mi: ss') Time from all_Objects
Note: The first record is the same as the last line.
You can create a function to handle this problem.
Create Or Replace Function Sys_Date Return Date IS
Begin
Return sysdate;
END;
Select to_char (sys_date, 'hh: mi: ss') from all_Objects;
12.
Number of hours
SELECT EXTRACT (HOUR from timestamp '2001-02-16 2:38:40') from offer
SQL> 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, 'HH24') from dual;
Sysdate to_char (sysdate, 'hh24')
-------------------------------------------
2003-10-13 19:35:21 19
Get the Year of the Year and this similar
13.
Annual Month 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.
Handling the number of monthly days
SELECT TO_CHAR (ADD_MONTHS (Last_Day (Sysdate) 1, -2), 'YYYYMMDD', Last_DAY (SYSDATE) from DUAL
16.
Find the number of days this year
SELECT Add_MontHS (Trunc (Sysdate, 'Year'), 12) - Trunc (Sysdate, 'Year') from DUAL
Leap year treatment method
TO_CHAR (Last_Day (to_date ('02 '||),' DD ')
If it is 28, it is not a leap year.
17.
The difference between YYYY and RRRR
'YYYY99 TO_C
------- ----
YYYY 99 0099
RRRR 99 1999
YYYY 01 0001
RRRR 01 2001
18. Processing of different time zones
Select to_char (new_time (sysdate, 'gmt', 'estat),' DD / MM / YYYY HH: MI: SS '), SYSDATE
From Dual;
19.
5 seconds an interval
Select to_date (floor (to_char (sysdate, 'ssss') / 300) * 300,' sssss'), to_char (sysdate, 'sssss ")
From dual
2002-11-1 9:55:00 35786
SSSSS represents 5-digit seconds
20.
Day a year
Select to_char (sysdate, 'ddd'), Sysdate from Dual
310 2002-11-6 10:03:51
21. Calculate hours, points, seconds, milliseconds
SELECT
Days,
A,
Trunc (A * 24) Hours,
Trunc (A * 24 * 60 - 60 * Trunc (A * 24)) Minutes,
Trunc (A * 24 * 60 * 60 - 60 * Trunc (A * 24 * 60)) Seconds,
Trunc (A * 24 * 60 * 60 * 100 - 100 * Trunc (A * 24 * 60 * 60)) 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, 'YYYYMMDDHH24MISS');
//
FLOOR (Date2-Date1) / 365)
Floor ((Date2-Date1, 365) / 30) as a month
MOD (MOD (Date2-Date1, 365), 30) as the day.
23.NEXT_DAY function
Next_day (sysdate, 6) is from the next Friday next Friday. The numbers behind are counted from Sunday. 1 2 3 4 5 6 7
Day and two three four five six
