SQL date-related functions and oracle date-related functions

SQL date functions

-- DATEADD
-- Returns a new datetime value based on a specified date.
-- DATEADD (datepart, number, date)
-- Abbreviation of date
-- Year yy, yyyy
-- Quarter qq, q
-- Month mm, m
-- Dayofyear dy, y
-- Day dd, d
-- Week wk, ww
-- Hour hh
-- Minute mi, n
-- Second ss, s
-- Millisecond MS
SELECT dateadd (mm, 5, '2014/1/8080') as date -- DATEADD
-- Returns a new datetime value based on a specified date.
-- DATEADD (datepart, number, date)
-- Abbreviation of date
-- Year yy, yyyy
-- Quarter qq, q
-- Month mm, m
-- Dayofyear dy, y
-- Day dd, d
-- Week wk, ww
-- Hour hh
-- Minute mi, n
-- Second ss, s
-- Millisecond MS
SELECT dateadd (mm, 5, '2014/1/123') as date

-- Return Value: 00:00:00. 000

-- DATEDIFF
-- Returns the number of date and time boundaries across two specified dates.
--
-- Syntax
-- DATEDIFF (datepart, startdate, enddate)
-- Abbreviation of date
-- Year yy, yyyy
-- Quarter qq, q
-- Month mm, m
-- Dayofyear dy, y
-- Day dd, d
-- Week wk, ww
-- Hour hh
-- Minute mi, n
-- Second ss, s
-- Millisecond MS
Select datediff (yy, '2017-01-01 ', getdate ())
-- Return Value: 106

-- DATENAME
-- Returns a string representing the specified date part of the specified date.
--
-- Syntax
-- DATENAME (datepart, date)
-- Abbreviation of date
-- Year yy, yyyy
-- Quarter qq, q
-- Month mm, m
-- Dayofyear dy, y
-- Day dd, d
-- Week wk, ww
-- Hour hh
-- Minute mi, n
-- Second ss, s
-- Millisecond MS
Select datename (year, getdate () + 'Year' + DATENAME (month, getdate () + 'month' AS 'monthname'

Oracle date functions

Description of date format parameters
D. day of the week
The name of DAY, which is filled with spaces to 9 characters.
Day of DD month
The day of the year in DDD
Short Name of DY day
Week of the Year of the iw iso Standard
Four-digit year of the iyyy iso Standard
YYYY four-digit year
Last three digits of YYY, YY, and Y years, two digits, one digit
HH hours, at 12 hours
HH24 hours, in 24 hours
MI score
SS seconds
MM Month
Abbreviated month of Mon
Full name of Month
W the week of the month
The week in WW

1. Date interval operation
Current Time minus 7 minutes
Select sysdate, sysdate-interval '7' MINUTE from dual
Current Time minus 7 hours
Select sysdate-interval '7' hour from dual
Current Time minus 7 days
Select sysdate-interval '7' day from dual
Current Time minus July
Select sysdate, sysdate-interval '7' month from dual
Current Time minus 7 years
Select sysdate, sysdate-interval '7' year from dual
Time Interval multiplied by a number
Select sysdate, sysdate-8 * interval '2' hour from dual
2. Date-to-character operations
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh24: mi: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm-dd hh: mi: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-ddd hh: mi: ss') from dual
Select sysdate, to_char (sysdate, 'yyyy-mm iw-d hh: mi: ss') from dual
Refer to the oracle documentation (ORACLE901DOC/SERVER.901/A90125/SQL _ELEMENTS4.HTM #48515)
3. Character-to-date operations
Select to_date ('1970-10-17 21:15:37 ', 'yyyy-mm-dd hh24: mi: ss') from dual
The usage is similar to that of to_char.
4. Use of the trunk/ROUND Function
Select trunc (sysdate, 'Year') from dual
Select trunc (sysdate) from dual
Select to_char (trunc (sysdate, 'yyyy'), 'yyyy') from dual
5. oracle has millisecond-level data types
-- Returns the current time year month hour minute second millisecond
Select to_char (current_timestamp (5), 'DD-MON-YYYY HH24: MI: SSxFF') from dual;
-- Returns the second millisecond of the current time. The precision after the second can be specified (max = 9)
Select to_char (current_timestamp (9), 'mi: SSxFF ') from dual;
6. computing program running time (MS)
Declare
Type rc is ref cursor;
Rochelle rc;
Rochelle dummy all_objects.object_name % type;
Rochelle start number default dbms_utility.get_time;
Begin
For I in 1000
Loop
Open l_rc
'Select object_name from all_objects '|
'Where object_id = '| I;
Fetch l_rc into l_dummy;
Close l_rc;
End loop;
Dbms_output.put_line
(Round (dbms_utility.get_time-l_start)/100, 2) |
'Seconds ...');
End;