SQL date-related functions and oracle date-related functions

Source: Internet
Author: User
Tags oracle documentation

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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.