SQL-9. database functions

(*) Numeric Functions

In the execution remarksCode

• ABS (): Returns the absolute value.
• Ceiling (): Round to the maximum integer. 3.33 is rounded to 4, 2.89 is rounded to 3, and-3.61 is rounded to-3.
  Ceiling → ceiling
• Floor (): Round to the smallest integer. 3.33 is rounded to 3, 2.89 is rounded to 2, and-3.61 is rounded to-4.
  Floor → floor.
• Round (field, decimal places):Rounding. Round to "number nearest to my radius ". Round → "radius ". Round (3.1425, 2 ).

(*) String functions

• Len (): Calculates the string length.
• Lower (),Upper (): Lower to lower-case and upper-case
• Ltrim (): Remove spaces from the left side of the string.
• Rtrim (): Remove spaces at the right of the string
• Ltrim (rtrim ('bb '): removes spaces on both sides.
• Substring (String,Start_position,Length)
  ParametersStringMain string,Start_positionIs a substring,
  Starting position in the main string,LengthThe maximum length of a substring.

 
Select substring ('abcdef111', 2, 3)
 

 

Date Functions

Getdate(): Get the current date and time

Dateadd(Datepart, number, date)To calculate the date after the addition.

Datediff(Datepart, startdate, enddate): Calculate the difference between two dates.

Datepart(Datepart, date): Returns a specific part of a date.

 

• Getdate (): Get the current date and time

  ========================================================== ==================

• Dateadd (datepart, number, date) To calculate the date after the addition. The date parameter is the date to be calculated, the number parameter is incremental, and the datepart parameter is the unit of measurement. For optional values, see the remarks.

  Dateadd (day, 3, date)For date calculation3 days laterDate

  The value is 7.31 three days after January 28 ,.
  

  WhileDateadd (month,-8, date)For date calculation8 months agoDate

   
  SelectDateadd(Month,-8,Getdate())
   

  2012.7 minus 8 months: 2011.11
  
  

  ========================================================== ==================

• Datediff (datepart, startdate, enddate): Calculate the difference between two dates.

  Datepart is the unit of measurement. For more information, see dateadd.
  
  Employee Start Time

 
Select fname, findate, datediff (year, findate, getdate () from t_employee
 

Count the number of employees of different service ages:

 
Select datediff (year, findate, getdate (), count (*) from t_employee group by datediff (year, findate, getdate ())
 

========================================================== ==================

• Datepart (datepart, date): Returns a specific part of a date.

  Count the number of employees' employment years:

  Select datepart (year, findate), count (*) from t_employee

  Group by datepart (year, findate)

========================================================== ==================

 

 

Type conversion functions

Cast (expression as data_type)

Convert (data_type, expression)

 

Cast

Convert

 

 select fidnumber, right (fidnumber, 3) as the last three digits, cast (right (fidnumber, 3) as integer) as the last three digits of the integer form, cast (right (fidnumber, 3) as integer) + 1 as the last three digits plus 1, convert (integer, right (fidnumber, 3)/2 as the last three digits divided by 2 from t_person