DB2 function Daquan

DB2 function An example of an explanation function for a function name function AVG () returns the average of a set of values. Selectavg (SALARY) Frombsempms; CORR (), CORRELATION () returns the relationship coefficient of a pair of values. SELECT CORRELATION (Salary,bonus) from Bsempms; COUNT () returns the number of rows or values in a set. SelectCount (*) Frombsempms; 　　COVAR (), covariance () returns the covariance of a pair of values. Selectcovar (Salary,bonus) Frombsempms; 　　Max () returns the maximum value in a set of values. Selectmax (SALARY) Frombsempms; 　　MIN () returns the minimum value in a set of values. Selectmin (SALARY) Frombsempms; 　　STDDEV () returns the standard deviation of a set of values. Selectstddev (SALARY) Frombsempms; 　　SUM () to return the sum of a set of data. Selectsum (SALARY) Frombsempms; 　　VAR (), VARIANCE () returns the variance of a set of values. Selectvariance (SALARY) Frombsempms; 　　ABS (), Absval () returns the absolute value of the parameter. Selectabs ( -3.4) Frombsempms; 　　ACOS () returns the inverse cosine value of the parameter. Selectacos (0.9) Frombsempms;　　ASCII () returns the ASCII code of the leftmost character of an integer parameter. Selectascii (' R ') Frombsempms;　　ASIN () returns the inverse sine function of the parameter of the angle expressed in radians. Selectasin (0.9) Frombsempms;　　ATAN () returns the inverse tangent of the parameter, which is the parameter of the angle expressed in radians. Selectatan (0.9) Frombsempms;　　ATAN2 () returns the inverse tangent of the x and y coordinates of the angle expressed in radians. SELECTATAN2 (0.5,0.9) Frombsempms; BIGINT () returns a 64-bit integer of a number or string in an integer constant representing the Selectbigint (emp_no) Frombsempms; CEILING () OR CEIL () returns a string representation of a DateTime, String, Integer, Decimal, or double-precision floating-point number that is greater than or equal to the minimum integer value of the parameter CHAR (). Selectchar (SALARY, ', ') Frombsempms; CHR () returns the character Selectchar (167) Frombsempms with the ASCII code specified by the parameter; CONCAT () Returns a connection of two strings. Selectconcat (Emp_no,emp_nam) Frombsempms; Year () returns the yearly portion of the value. Selectyear (' 2003/01/02 ') Frombsempms; VARCHAR () returns a string representation of a variable length string, a date type, and a graph string representing Selectvarchar (emp_nam,50) Frombsempms; UCASE () or UPPER () returns the upper case of the string TRUNCATE () or TRUNC () truncates from the position to the right of the decimal point of the expression and returns the value. Selecttruncate (345.6789,2) Frombsempms; Time () returns a value of Selecttime (' 2001-03-19.12.30.123456 ') Frombsempms; SUBSTR (EXP1,EXP2) returns the substring starting at EXP2 from the EXP1 string. SQRT () returns the square root of the parameter. SELECTSQRT (FROMBSEMPMS); Space () returns the length specified by the parameter, including a string containing spaces. Selectspace (Ten) Frombsempms; SECOND () returns the second part of a numeric value. Selectsecond (' 18:34:32 ') Frombsempms; 　　RTRIM () deletes the trailing space of the string. Selectrtrim (' COMMENT ') Frombsempms; 　　ROUND (EXP1,EXP2) returns the rounded value starting at the EXP2 position to the right of the EXP1 decimal point. Selectround (2345.6789,2) Frombsempms replace (EXP1,EXP2,EXP3) uses EXP3 to replace all EXP1 EXP2 in Selectchar (replace (' romandd ', ' NDD ', ' CCB '), Frombsempms; REPEAT (EXP1,EXP2) returns the character after the EXP1 repeats EXP2 timesString. Selectchar (REPEAT (' REPEAT ', 3), +) Frombsempms; 　　REAL () returns a single-precision floating-point number representation of a numeric value. Selectreal (Ten) Frombsempms; RAND () returns the random floating-point number between 0 and 1. Selectrand () Frombsempms; Power (EXP1,EXP2) returns the EXP2 power of EXP1. Selectpower (2,5) Frombsempms; POSSTR (EXP1,EXP2) returns the position of the EXP2 in EXP1. SELECT (' abcdefgh ', ' D ') Frombsempms; Nullif (EXP1,EXP2) is null if EXP1=EXP2, otherwise EXP1 nodenumber () returns the partition number of the row. Selectnodenumber (emp_no) Frombsempms; Month () returns the monthly portion of a numeric value. Selectmonth (' 2003/10/20 ') Frombsempms; 　　MOD (EXP1,EXP2) returns the remainder of EXP1 divided by EXP2. Selectmod (20,8) Frombsempms MINUTE () returns the minute portion of a numeric value. Selectminute (' 18:34:23 ') Frombsempms; LTRIM () Deletes the spaces in front of the string. Selectltrim (' cddd ') Frombsempms; 　　HOUR () returns the hour portion of a numeric value. Selecthour (' 18:34:23 ') Frombsempms; 　　DOUBLE () If the argument is a numeric expression, returns the floating-point number that corresponds to it, and returns the string expression for the argument if it is a string expression. Selectdouble (' 5678 ') Frombsempms; EXP () returns the exponential function of the parameter. Selectexp (2) Frombsempms; Float () returns a floating point representation of a number. Selectfloat (789) Frombsempms; Floor () returns the largest integer less than or equal to the parameter. Slectfloor (88.93) Frombsempms; HEX () returns a 16-binary representation of a value represented as a string. Selecthex (FROMBSEMPMS); Dayname returns a mixed-case string that, for the day part of the argument, represents the day's name in the week (for example,Friday). DAYOFWEEK returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Sunday. Dayofweek_iso returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Monday. DayOfYear returns the day ordinal of a year in a parameter, expressed as an integer value with a range of 1-366. Days returns an integer representation of the date. Julian_day returns the number of days from January 1, 4712 BC (the start date of the Julian calendar) to the specified date value in the parameter, expressed as an integer value. Midnight_seconds returns the number of seconds between midnight and the time value specified in the parameter, expressed as an integer value ranging from 0 to 86400. MONTHNAME returns a mixed-case string (for example, January) for months of the month portion of the parameter. Timestamp_iso returns a timestamp value based on a date, time, or timestamp parameter. Timestamp_format returns a timestamp from a string that has been interpreted using a character template. Timestampdiff returns the estimated time difference represented by the type defined by the first parameter, based on the difference between two timestamps. To_char returns the character representation of a timestamp that has been formatted with a character template. To_char is synonymous with Varchar_format. To_date returns a timestamp from a string that has been interpreted using a character template. To_date is synonymous with Timestamp_format. WEEK returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-54. Take Sunday as the beginning of the week.                          Week_iso returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-53.     To adjust the current time or the current time stamp to Gmt/cut, subtract the current time or time stamp from the current timezone register: time-current timezone timestamp-current TimeZone Given a date, time, or time stamp, the appropriate function can be used to extract (if applicable) the year, month, day, hour, minute, second, and microsecond parts individually: Timestamp MONTH (current timestamp) Day (Curren T timestamp) HOUR (current timestamp) MINUTE (current TimEstamp) SECOND (current timestamp) microsecond (current timestamp) because there are no better terms, you can also use English to perform date and time calculations: current date + 1 year Current date + 3 years + 2 MONTHS + Day Current time + 5 HOURS-3 MINUTES + SECONDS Extract the date and time separately from the timestamp is also very Simple: DATE timestamp time (current timestamp), and the following example describes how to get the present timestamp of a microsecond partial zeroing: the Timestamp-microsecond (current timestamp) microseconds if you want to link a date or time value to other text, you need to convert the value to a string first. To do this, simply use the char () function: char (current date) char (current time) char (current date + hours) to convert the string to a date or time value, you can use: T  Imestamp (' 2002-10-20-12.00.00.000000 ') TIMESTAMP (' 2002-10-20 12:00:00 ') date (' 2002-10-20 ') date (' 10/20/2002 ') time  (' 12:00:00 ') Time (' 12.00.00 ') TIMESTAMP (), DATE (), and time () functions accept more than one format.     The above several formats are just examples, and I'll use it as an exercise to let the reader find other formats on their own. Sometimes, you need to know the difference between two time stamps. To do this, DB2 provides a built-in function called Timestampdiff (). However, the function returns an approximate value because it does not consider a leap year and assumes only 30 days per month. The following example describes how to get an approximate time difference of two dates: Timestampdiff (<n>, char (timestamp (' 2002-11-30-00.00.00 ')-Timestamp (' 2002-11-08-00.00.00 ')) for <n>, you can use the following values instead to indicate the time unit of the result: 1 = seconds of the decimal part 2 = seconds 4 = Minute 8 = 16 = Day 32 = Week 64 = Month 128 = Quarter 256 = year when the date is close, use Timestampdiff () to be precise when the date is very large.     If more accurate calculations are required, you can use the following methods to determine the time difference (in seconds): (Days (T1)-Days (T2)) * 86400 + (midnight_seconds (T1)-midnight_seconds (T2)) For convenience, you can also create SQL user-defined functions for the above methods: Create function Secondsdiff (T1 TIMESTAMP, T2 TIMESTAMP) RETURNS INT RETURN ((Days (T1 )-Days (T2)) * 86400 + (midnight_seconds (T1)-midnight_seconds (T2)) @ If you need to determine whether a given year is a leap years, here is a useful SQL function that you can create           To determine the number of days in a given year: CREATE FUNCTION daysinyear (yr int) RETURNS int RETURN (case (mod (yr)) while 0 then 366 ELSE      Case (mod (yr, 4)) while 0 then case (mod (yr, +)) when 0 then 365 ELSE 366 end else 365 end end) @ Finally, the following is a built-in function table for date operations. It is designed to help you quickly identify functions that may meet your requirements, but does not provide a complete reference.     For more information about these functions, refer to the SQL Reference encyclopedia.   SQL Date and Time function Dayname returns a mixed-case string, for the day part of the parameter, the name of the day (for example, Friday).   DAYOFWEEK returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Sunday. DayofWeek_iso returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Monday.   DayOfYear returns the day ordinal of a year in a parameter, expressed as an integer value with a range of 1-366.   Days returns an integer representation of the date.   Julian_day returns the number of days from January 1, 4712 BC (the start date of the Julian calendar) to the specified date value in the parameter, expressed as an integer value.   Midnight_seconds returns the number of seconds between midnight and the time value specified in the parameter, expressed as an integer value ranging from 0 to 86400.   MONTHNAME returns a mixed-case string (for example, January) for months of the month portion of the parameter.   Timestamp_iso returns a timestamp value based on a date, time, or timestamp parameter.   Timestamp_format returns a timestamp from a string that has been interpreted using a character template.   Timestampdiff returns the estimated time difference represented by the type defined by the first parameter, based on the difference between two timestamps. To_char returns the character representation of a timestamp that has been formatted with a character template.   To_char is synonymous with Varchar_format. To_date returns a timestamp from a string that has been interpreted using a character template.   To_date is synonymous with Timestamp_format. WEEK returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-54.   Take Sunday as the beginning of the week. Week_iso returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-53. DB2 Date Action function DB2 date operation function Current time: The present date, the time of year, the month, the day, the hour, minute, second, and microseconds of the times, and every part of the moment, and the seconds, and the milliseconds of the past: MON TH (current timestamp) day (current timestamp) HOUR (current timestamp) MINUTE (current timestamp) SECOND (current Timesta MP) Microsecond (current timestamp) time Plus/minus: current date + 1 years current date + 3 years + 2 MONTHS + Day Current + 5 HOURS-3 MINUTES + SECONDS to calculate the number of days between two dates, you can subtract from the date: Days (current date)-Day (date (' 1999-10-22 ')) time is converted to a string: Char The (current date) string is converted to time: TIMESTAMP (), date (), and Times (), the supported string format requires reference Help, and there are some functions that do not have Oracle's to_date flexible Data (' 2005-01-01 ') Date and time: Dayname returns a mixed-case string, for the day part of the parameter, the name of the day (for example, Friday). DAYOFWEEK returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Sunday. Dayofweek_iso returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Monday. DayOfYear returns the day ordinal of a year in a parameter, expressed as an integer value with a range of 1-366. Days returns an integer representation of the date. Julian_day returns the number of days from January 1, 4712 BC (the start date of the Julian calendar) to the specified date value in the parameter, expressed as an integer value. Midnight_seconds returns the number of seconds between midnight and the time value specified in the parameter, expressed as an integer value ranging from 0 to 86400. MONTHNAME returns a mixed-case string (for example, January) for months of the month portion of the parameter. Timestamp_iso returns a timestamp value based on a date, time, or timestamp parameter. Timestamp_format returns a timestamp from a string that has been interpreted using a character template. Timestampdiff returns the estimated time difference represented by the type defined by the first parameter, based on the difference between two timestamps. To_char returns the character representation of a timestamp that has been formatted with a character template. To_char is synonymous with Varchar_format. To_date returns a timestamp from a string that has been interpreted using a character template. To_date is synonymous with Timestamp_format. WEEK returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-54. Take Sunday as the beginning of the week. Week_iso returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-53. Because there are no better terms, you can also use English to perform date and time calculations: current Date + 1 Year current date + 3 years + 2 MONTHS + Day Current time + 5 HOURS-3 MINUTES + SECONDS to calculate the number of days between two dates , you can subtract from the date as follows: Days (the current date)-Days (date (' 1999-10-22 ')) and the following example describes how to obtain a microsecond partial zeroing of the present time stamp: Timestamp-m Icrosecond (current timestamp) microseconds if you want to link a date or time value to another text, you need to convert the value to a string first. To do this, simply use the char () function: char (current date) char (current time) char (current date + hours) to convert the string to a date or time value, you can use: timest AMP (' 2002-10-20-12.00.00.000000 ') TIMESTAMP (' 2002-10-20 12:00:00 ') date (' 2002-10-20 ') date (' 10/20/2002 ') Time (' 12:00:00 ') Time (' 12.00.00 ') if you want to convert the current date format into a custom format (such as ' YYYYMMDD '), how do you do it?    In my experience, the best way is to write a custom format function. Here is the code for this UDF: Create function ts_fmt (ts timestamp, FMT varchar) returns varchar () return with TMP (DD,MM,YYYY,HH, MI,SS,NNNNNN) as (select substr (digits (TS), 9), substr (digits (month (TS)), 9), RTrim (char (year (TS) )), substr (digits (hour (TS)), 9), substr (digits (minute (TS)), 9), SubSTR (Digits (second (TS)), 9), RTrim (char (microsecond (TS))) from sysibm.sysdummy1) Select Case FMT when ' yy YYMMDD ' then yyyy | | MM | | DD when ' mm/dd/yyyy ' then mm | | '/' | | DD | | '/' | | yyyy when ' yyyy/dd/mm hh:mi:ss ' then yyyy | | '/' | | MM | | '/' | | DD | |                 "| | HH | | ': ' | | Mi | | ': ' | | SS when ' nnnnnn ' then nnnnnn Else ' date format ' | |          COALESCE (FMT, ') | |     ' not recognized. ' End from TMP recommended to find a DB2 based on the east to see, than here to ask a lot faster, although others find the time also fast!!   SQL Date and Time function Dayname returns a mixed-case string, for the day part of the parameter, the name of the day (for example, Friday).   DAYOFWEEK returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Sunday.   Dayofweek_iso returns the day of the week in the parameter, expressed as an integer value in the range of 1-7, where 1 represents Monday.   DayOfYear returns the day ordinal of a year in a parameter, expressed as an integer value with a range of 1-366.   Days returns an integer representation of the date.   Julian_day returns the number of days from January 1, 4712 BC (the start date of the Julian calendar) to the specified date value in the parameter, expressed as an integer value.   Midnight_seconds returns the number of seconds between midnight and the time value specified in the parameter, expressed as an integer value ranging from 0 to 86400. MONTHNAME returns a mixed-case string for months of the month part of the parameter (for example, JaNuary).   Timestamp_iso returns a timestamp value based on a date, time, or timestamp parameter.   Timestamp_format returns a timestamp from a string that has been interpreted using a character template.   Timestampdiff returns the estimated time difference represented by the type defined by the first parameter, based on the difference between two timestamps. To_char returns the character representation of a timestamp that has been formatted with a character template.   To_char is synonymous with Varchar_format. To_date returns a timestamp from a string that has been interpreted using a character template.   To_date is synonymous with Timestamp_format. WEEK returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-54.   Take Sunday as the beginning of the week.          Week_iso returns the week ordinal of a year in a parameter, expressed as an integer value with a range of 1-53.  --------------------------------------------------------------------------------mymm reply at: 2003-08-04 14:22:38 Example:year (current timestamp), MONTH (current timestamp), current timestamp, HOUR (current timestamp) minut E (current timestamp) SECOND (current timestamp) microsecond (current timestamp) reference: Mymm reply to: 2003-08-08 17:      The 01:11 days function returns an integer representation of the date. To calculate the number of days between two dates, you can: Day of day (date (' 2000-9-1 ')) to use SQL to obtain the current date, time, and time stamp, refer to the appropriate DB2 register: Select current Date from sysibm.sysdummy1 SELECT current TIME from sysibm.sysdummy1 SELECT the timestamp from sysibm.sysdummy1 sysibm.sysdummy1 table is a special in-memory table that you can use to discover the db shown above The value of the 2 register. You can also use the keyword values to evaluate a register or an expression. For example, on the DB2 command line PROCESSOR,CLP, the following SQL statement reveals similar information: Values current date values current time values current Timestamp in the rest of the examples, I'll just provide a function or an expression instead of repeating select ...     From sysibm.sysdummy1 or using the values clause.     To adjust the current time or the current time stamp to Gmt/cut, subtract the current time or time stamp from the current timezone register: time-current timezone timestamp-current TimeZone Given a date, time, or time stamp, the appropriate function can be used to extract (if applicable) the year, month, day, hour, minute, second, and microsecond parts individually: Timestamp MONTH (current timestamp) Day (c urrent timestamp) HOUR (current timestamp) MINUTE (current timestamp) SECOND (current timestamp) microsecond (current Timestamp) It is also very simple to extract the date and time from the timestamp individually: date (current timestamp) time (current timestamp) because there is no better term, you can also use English to perform dates and time Calculation: current date + 1 years current date + 3 years + 2 MONTHS + Day current + 5 HOURS-3 MINUTES + SEC Onds to calculate days between two datesNumber, you can subtract from the date as follows: Days (the current date)-Days (date (' 1999-10-22 ')) and the following example describes how to obtain a microsecond partial zero-now timestamp: TIMESTAMP -Microsecond (current timestamp) microseconds if you want to link a date or time value to another text, you need to convert the value to a string first. To do this, simply use the char () function: char (current date) char (current time) char (current date + hours) to convert the string to a date or time value, you can use: T  Imestamp (' 2002-10-20-12.00.00.000000 ') TIMESTAMP (' 2002-10-20 12:00:00 ') date (' 2002-10-20 ') date (' 10/20/2002 ') time  (' 12:00:00 ') Time (' 12.00.00 ') TIMESTAMP (), DATE (), and time () functions accept more than one format.    The above several formats are just examples, and I'll use it as an exercise to let the reader find other formats on their own.

　　

DB2 function Daquan