Functions in SQL Server

Built IN Functions:

SQL provides many built-in functions to perform operations on data. These functions are useful while performing mathematical calculations, string concatenations, sub-strings etc. 

Scalar functions

Numeric Functions

Function	Description
abs(-10.67)	This returns absolute number of the given number means 10.67.
rand(10)	This will generate random number of 10 characters.
round(17.56719,3)	This will round off the given number to 3 places of decimal means 17.567

Character Functions

Function	Description
upper('dotnet')	This will returns upper case of given string means 'DOTNET'
lower('DOTNET')	This will returns lower case of given string means 'dotnet'
ltrim(' dotnet')	This will remove the spaces from left hand side of 'dotnet' string.
LEN(‘dotnet’)	Returns no of characters in the string given.(6)
LEFT('Dotnet',3)	Returns  first 3 characters from the string left side of the string.(DOT)
RIGHT('Dotnet',3)	Returns  last 3 characters from the string Right side of the string.(NET)
SUBSTRING('Dotnet',3,2)	Returns  2 characters starting from 3 character. (tn)

Conversion Functions

Function	Description
SELECT CONVERT(DECIMAL(5,2),15)	Returns 15.00
CONVERT (INT, 15.56)	This will convert the given float value to integer means 15.

DECLARE @A varchar(2)

DECLARE @B varchar(2)

DECLARE @C varchar(2)

set @A=25

set @B=15

set @C=33

Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result

-------------------------

Result

73

---------------------------

DECLARE @A1 INT

DECLARE @B1 INT

DECLARE @C1 INT

set @A1=25

set @B1=15

set @C1=33

Select @A1+@B1+@C1 as Result

-------------------------

Result

73

---------------------------

DECLARE @A2 varchar(2)

DECLARE @B2 varchar(2)

DECLARE @C2 varchar(2)

set @A2=25

set @B2=15

set @C2=33

Select @A2+@B2+@C2 as Result

-------------------------

Result

251533

---------------------------

Date Functions:

Date functions are used to convert date from one format to another.

SELECT GETDATE() - Returns current date

SELECT DATEADD(DAY,5,GETDATE()), - Adds 5 days to the current date.

SELECT DATEDIFF(yyyy,'2011/01/01','2017/01/01'). Show is difference between the given two days.

based on the iintervel given, returns difference in days, months , years etc... (check the table given) format.

Syntax : 

DATEDIFF(datepart,startdate,enddate)

datepart	Abbreviation
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Convert: 

Convert function is to convert one date format to another date format.

Syntax:

CONVERT(data_type(length),expression,style)

SELECT CONVERT(VARCHAR(19),GETDATE())

SELECT CONVERT(VARCHAR(10),GETDATE(),10)

SELECT CONVERT(VARCHAR(10),GETDATE(),110)

SELECT CONVERT(VARCHAR(11),GETDATE(),6)

SELECT CONVERT(VARCHAR(11),GETDATE(),106)

SELECT CONVERT(VARCHAR(24),GETDATE(),113)

-----------------------------------------------

Result 

Jul 14 2016  2:25PM

07-14-16

07-14-2016

14 Jul 16

14 Jul 2016

14 Jul 2016 14:25:39:693

----------------------------------------------------------

DATEPART

Date part function returns the required part from the date given.

SELECT DATEPART(yyyy,GETDATE())

SELECT DATEPART(mm,GETDATE())

SELECT DATEPART(dd,GETDATE())

SELECT DATEPART(day,GETDATE())

SELECT DATEPART(dy,GETDATE())

----------------------------------------------------

Result:

2016

7

14

14

196

--------------------------------------------------------

Date/time part	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
day of year	dy, y
day	dd, d
week	wk, ww
weekday	dw
hour	hh
minute	mi, n
second	ss, s
millisecond	ms

Aggregate functions:

 MIN, MAX, SUM, AVG, COUNT

Aggregate Function	Description
max()	This returns maximum value from a collection of values.
min()	This returns minimum value from a collection of values.
avg()	This returns average of all values in a collection.
count()	This returns no of counts from a collection of values.

CREATE TABLE #tmp_Aggfun(

StudentName VARCHAR(50),

Sub VARCHAR(50),

Marks INT

)

INSERT INTO #tmp_Aggfun VALUES ('S1','Telugu',95),('S1','English',90),('S1','Maths',99),('S1','Science',85),('S1','Social',79)

INSERT INTO #tmp_Aggfun VALUES ('S2','Telugu',64),('S2','English',91),('S2','Maths',98),('S2','Science',86),('S2','Social',71)

INSERT INTO #tmp_Aggfun VALUES ('S3','Telugu',74),('S3','English',92),('S3','Maths',97),('S3','Science',87),('S3','Social',72)

INSERT INTO #tmp_Aggfun VALUES ('S4','Telugu',75),('S4','English',93),('S4','Maths',96),('S4','Science',82),('S4','Social',74)

INSERT INTO #tmp_Aggfun VALUES ('S5','Telugu',76),('S5','English',94),('S5','Maths',95),('S5','Science',81),('S5','Social',78)

SELECT * FROM #tmp_Aggfun

SELECT  MAX(Marks) FROM #tmp_Aggfun WHERE Sub='Telugu'

SELECT  MIN(Marks) FROM #tmp_Aggfun WHERE Sub='Telugu'

SELECT  AVG(Marks) FROM #tmp_Aggfun WHERE Sub='Telugu'

SELECT  COUNT(Marks) FROM #tmp_Aggfun WHERE Sub='Telugu'

SELECT  SUM(Marks) FROM #tmp_Aggfun WHERE Sub='Telugu'

-------------------------

Result

95

64

76

5

384

--------------------------------