SQL prerequisite-ORACLE-SQSLSERVER-DB2 Time function and summary of common functions

SQL Server Time article: *************************************************************
One, Time function
--getdate Get current time
Select GETDATE ()

--dateadd Original time Add: 2013-02-17 13:20:16 this time plus 12 months
Select DATEADD (month,12, ' 2013-02-17 13:20:16 ')--return: 2014-02-17 13:20:16.000 (parameter MONTH can be changed to day,year etc. date plus corresponding value)

--datediff Two time difference (back-front = return value)
Select DateDiff (Day, ' 2013-02-01 ', ' 2013-02-18 ')--return: 17 (parameter days can be changed to Month,year date plus corresponding value)

--datepart gets a partial integer of the date
Select DATEPART (Month, ' 2013-2-17 ')--returns 2 (parameter month can be changed to Day,year date plus corresponding value)

--datename gets the string for the specified part
Select Datename (Weekday, ' 2013-2-17 ')--Return to Sunday (parameter weekday can be changed to Day,year date plus corresponding value)

--day (), Month (), year () gets the string for the specified part
Select Day (' 2013-2-15 ')--return 15

Second, time format conversion

Select CONVERT (varchar, GETDATE (), 120)--Return 2013-02-17 13:37:54
Select Replace (replace (CONVERT (varchar, GETDATE (), 120), '-', '), ' ', '), ': ', ')--return 20130217133828
Select CONVERT (varchar), GETDATE (), 111)--return 2013/02/17
Select CONVERT (varchar), GETDATE (), 112)--Return 20130217
Select CONVERT (varchar), GETDATE (), 102)--Return 2013.02.17
Select CONVERT (varchar), GETDATE (), 101)--Return 02/17/2013
Select CONVERT (varchar), GETDATE (), 103)--Return 17/02/2013
Select CONVERT (varchar), GETDATE (), 104)--Return 17.02.2013
Select CONVERT (varchar), GETDATE (), 105)--Return 17-02-2013
Select CONVERT (varchar), GETDATE (), 106)--return 17 02 2013
Select CONVERT (varchar), GETDATE (), 107)--return 02 17, 2013
Select CONVERT (varchar), GETDATE (), 108)--Return 13:42:50
Select CONVERT (varchar), GETDATE (), 109)--return 02 17 2013
Select CONVERT (varchar), GETDATE (), 110)--return 02-17-2013
Select CONVERT (varchar), GETDATE (), 113)--Return 17 02 2013 1
Select CONVERT (varchar), GETDATE (), 114)--Return 13:42:24:743

Third, time format and other format conversion

convert int to time: convert (Datetime,left (dateid,8), 101)
Convert Time to Int:year (createdate) *10000 + month (createdate) *100 + day (createdate)
Convert time format [2014-07-24 10:32:43.197] to Int20140724:select cast (replace (char (), GETDATE (), +), '-', ') as int)

SQL Server function article: *************************************************************

One, character functions
ASCII (string expression)
char (integer-expression)
CHARINDEX (string expression 1, string-expression 2[, integer-expression])
Difference (string-expression 1, string-expression 2)
Left (string-expression, integer-expression)
Right (string-expression, integer-expression)
Len (string expression)
Lower (string expression)
Upper (string expression)
LTrim (string expression)
RTrim (string expression)
Patindex (string-expression 1, string-expression 2)
Reverse (string expression)
Space (integer-expression)
STR (Float type decimal [, total length [, number of digits retained after decimal point]])
Stuff (string expression 1, start position, length, string expression 2)
SUBSTRING (string expression, start position, length)
Replace (string-expression 1, string-expression 2, string-expression 3)
Second, mathematical functions
ABS (numeric expression)
cos (floating-point expression)
Sin (floating-point expression)
Cot (floating-point expression)
Ceiling (numeric expression)
Floor (numeric expression)
rand ([integer expression])
Round (numeric expression [, length [, Mode of Operation]])
Third, date function
DATEADD (date part, number, date)
DateDiff (date part, start date, end date)
Datename (date part, date)
DATEPART (date part, date)
Day (date) month (date) year (date)
Iv. Conversion Functions
Convert (data type [(length)], expression [, style])
Cast (expression as data type [(length)])
GETDATE ()
V. System and FUNCTION functions
Select NEWID ()
IsNumeric (arbitrary expression)
IsDate (arbitrary expression)
----------------------------------------------------------------------------
ORACLE Time article: *************************************************************

First, the current time split:
Select To_char (sysdate, ' yyyy ') as nowyear from dual; Get the year of the time
Select To_char (sysdate, ' mm ') as nowmonth from dual; Get the month of the time
Select To_char (sysdate, ' DD ') as nowday from dual; Get the day of the time
Select To_char (sysdate, ' Day ') from dual;
Select To_char (sysdate, ' hh24 ') as nowhour from dual; When you get the time
Select To_char (sysdate, ' mi ') as Nowminute from dual; Get the minutes of the time
Select To_char (sysdate, ' SS ') as Nowsecond from dual; Gets the seconds of the time
Select To_date (' 2004-05-07 13:23:44 ', ' yyyy-mm-dd hh24:mi:ss ') from dual

Second, the current time itself calculation:
Time minus 7 minutes for the current time
Select Sysdate,sysdate-interval ' 7 ' MINUTE from dual
The current time minus 7 hours of time
Select Sysdate-interval ' 7 ' hour from dual
The current time minus 7 days
Select Sysdate-interval ' 7 ' Day from dual
Time minus July for current time
Select Sysdate,sysdate-interval ' 7 ' month from dual
Time minus 7 years in the current time
Select Sysdate,sysdate-interval ' 7 ' year from dual

Third, the current time other calculations:
Start_date,end_date, calculate the time difference between the two dates (in days, hours, minutes, seconds, milliseconds, respectively):
Days: ROUND (To_number (sysdate-start_date))
Hours: ROUND (To_number (sysdate-start_date) * 24)
Minutes: ROUND (To_number (sysdate-start_date) * 24 * 60)
Seconds: ROUND (To_number (sysdate-start_date) * 24 * 60 * 60)
MS: ROUND (To_number (sysdate-start_date) * 24 * 60 * 60 * 1000)

Iv. Current Time Conversion

Time conversion to char:
Select To_char (sysdate, ' Yyyy-mm-dd hh24:mi:ss ') as nowtime from dual;
Char converted to TIME:
Select To_date (' 2003-10-17 21:15:37 ', ' yyyy-mm-dd hh24:mi:ss ') from dual


ORACLE function article: *************************************************************

One, character functions
Lower (char): Converts a string to a lowercase format.
Upper (char): Converts a string to an uppercase format.
Length (char): Returns the lengths of the strings.
substr (char, M, N): intercepts a substring of a string, n means n characters, not a delegate to Nth
Replace (CHAR1, search_string, replace_string)
InStr (C1,C2,I,J)--Determines whether a character or string exists, returns the index of where it appears, otherwise returns less than 1, searches for the specified character in a string, and returns the location of the specified character;
C1 string to be searched
C2 the string you want to search
I Search start position, default is 1
Where J appears, default is 1

Second, mathematical functions
Round (n,[m]) This function is used to perform rounding
Trunc (N,[m]) This function is used to intercept numbers.
MoD (m,n) take the remainder function
Floor (n) returns the largest integer less than or equal to n
Ceil (n) returns the smallest integer greater than or equal to n
ABS (n) returns the absolute value of the number n

Third, date function
Sysdate Return to System time
add_months function can get time before or after n months of a certain time
Last_day (d) Returns the last day of the month in which the specified date is located

Iv. Conversion Functions
To_date () function
To_char () function

V. System and FUNCTION functions
1) Terminal: The identifier of the terminal that the current session client corresponds to, such as the computer name
2) Language: Language
3) db_name: Current database name
4) Nls_date_format: the date format that the current session customer corresponds to
5) Session_user: The database user name for the current session client
6) Current_schema: The default scheme name corresponding to the current session customer
7) Host: Returns the name of the host where the database resides



Use of the Oracle trunc () function
/************** Date ********************/
1.select trunc (sysdate) from dual--2011-3-18 today's date is 2011-3-18
2.select trunc (sysdate, ' mm ') from dual--2011-3-1 returns the first day of the month.
3.select trunc (sysdate, ' yy ') from dual--2011-1-1 returns the first day of the year
4.select trunc (sysdate, ' DD ') from dual--2011-3-18 return current month day
5.select trunc (sysdate, ' yyyy ') from dual--2011-1-1 returns the first day of the year
6.select trunc (sysdate, ' d ') from dual--2011-3-13 (Sunday) returns the first day of the current week
7.select trunc (sysdate, ' hh ') from dual--2011-3-18 14:00:00 current time is 14:41
8.select trunc (sysdate, ' mi ') from dual--2011-3-18 14:41:00 trunc () function no seconds accurate
/*************** Digital ********************/
/*
TRUNC (Number,num_digits)
Number requires a truncated rounding.
The num_digits is used to specify the number of rounding precision. The default value for Num_digits is 0.
TRUNC () function is not rounded when truncated
*/
9.select trunc (123.458) from dual--123
10.select trunc (123.458,0) from dual--123
11.select trunc (123.458,1) from dual--123.4
12.select trunc (123.458,-1) from dual--120
13.select trunc (123.458,-4) from dual--0
14.select trunc (123.458,4) from dual--123.458
15.select trunc (123) from dual--123
16.select trunc (123,1) from dual--123
17.select trunc (123,-1) from dual--120
-------------------------------------------------

DB2:

--Get the current date: Select present on date from sysibm.sysdummy1;
Values current date;
--Get the current date of select time from Sysibm.sysdummy1;
Values current time;
--Gets the current timestamp of select present timestamp from SYSIBM.SYSDUMMY1;
Values current timestamp;
--To adjust the current time or current timestamp to gmt/cut, subtract the current time or time stamp from the current timezone register:
Values current time-current timezone;
Values current timestamp-current timezone;
--Get the current years of values year (timestamp);
--Get the current month values of timestamp;
--Get current daily Values day (timestamp);
--Get current time values hour (timestamp);
--get minutes of values minute (current timestamp);
--Get the second values second (current timestamp);
--Get MS values microsecond (current timestamp);
--Extract the date and time separately from the timestamp
Values date (current timestamp); 07/24/14
Values Varchar_format (current TIMESTAMP, ' yyyy-mm-dd ');
Values char (current date); 2014-07-24-10.46.51.978540
Values time (current timestamp); 10:47:25
--Calculation of execution date and time
Values current date+1 year;
Values current date+3 years+2 months +15 days;
Values current time +5 hours-3 minutes +10 seconds;
--Calculates the number of days between two dates
Values days (current date) – Days (date (' 2010-02-20 '));
--Change time and date to string
Values char (current date);
Values char (current time);
--to convert a string to a date or time value
Values timestamp (' 2010-03-09-22.43.00.000000 ');
Values timestamp (' 2010-03-09 22:44:36 ');
Values date (' 2010-03-09 ');
Values date (' 03/09/2010 ');
Values time (' 22:45:27 ');
Values time (' 22.45.27 ');
--Calculates the difference between two time stamps:
-The number of seconds is divided into units
Values Timestampdiff (1,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --Unit of seconds
Values Timestampdiff (2,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --Divided into units
Values Timestampdiff (4,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --Hour unit
Values Timestampdiff (8,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --Days for units
Values Timestampdiff (16,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --Week unit
Values Timestampdiff (32,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --month unit
Values Timestampdiff (64,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); --Quarterly units
Values Timestampdiff (128,char (current timestamp-timestamp (' 2010-01-01-00.00.00 ')); -The year unit
Values Timestampdiff (256,char (current timestamp-timestamp (' 2010-01-01-00.00.00 '));

Convert Time to Int:cast (To_char (in_date, ' YYYYMMDD ') as INT) as DateID

This article is from "sole weeping" blog, declined reprint!

SQL prerequisite-ORACLE-SQSLSERVER-DB2 Time function and summary of common functions