Common SqlServer built-in functions and usage

Common SqlServer built-in functions and usage

Select cast ('2014/1/03 'as datetime );

Select convert (varchar (10), GETDATE (), 120); -- convert to date type

Select case when id = 1 THEN 'a0 'else 'TT' END from demo1; -- case when

Select len ('ssa Edison ') -- returns 6 bytes

Select left ('sea', 2) -- se starts from the left with the first two characters

Select right ('sea', 2) -- aa two characters from the right

Select LTRIM ('ds') -- remove the left space Rtrim () to remove the right space

Select replace ('I hate you', 'hate', 'love') -- return I love you

Select STUFF ('test do it', 2, 4, 'hh') -- parameter description 1 original string 2 start position 3 end position 4 replace string

Select GETDATE (), GETUTCDATE ()

Select DATEDIFF (dd, GETDATE (), cast ('1970-03-27 'as datetime) -- returns the difference between two dates.

Select DATEADD (mm, 12, getdate (); -- specifies the abbreviation dd-day mm-month wk-week for adding a day or month.

Select DATENAME (dd, GETDATE () -- returns the string format of the specified part of the date.

Select abs (-12) -- returns the absolute value of the corresponding number

Select CEILING (4.2) -- 5 returns the smallest integer greater than or equal to the expression

Select floor (4.2) -- 4 returns the maximum integer less than or equal to the expression

Select power (2, 4) -- indicates the power 4 of 2

Select sign (-12) ---1 returns 1 negative for an integer and-1 0 returns 0

Select sqrt (25) -- 5 square root

Select ROUND (5.4, 0.1) -- rounding to the specified precision

Select REVERSE ('abc') -- CBA REVERSE output

Select substring ('dest0w', 2, 4); -- truncates four digits from the second

Select REPLICATE ('sa ', 3) -- repeated expression