create table #tbl
(
Row_No int identity,
Amount money
)
insert into #tbl(Amount)values(1000)
insert into #tbl(Amount)values(2000)
insert into #tbl(Amount)values(3000)
insert into #tbl(Amount)values(4000)
insert into #tbl(Amount)values(5000)
insert into #tbl(Amount)values(6000)
insert into #tbl(Amount)values(7000)
insert into #tbl(Amount)values(8000)
insert into #tbl(Amount)values(9000)
insert into #tbl(Amount)values(10000)
insert into #tbl(Amount)values(11000)
insert into #tbl(Amount)values(12000)
insert into #tbl(Amount)values(13000)
insert into #tbl(Amount)values(14000)
insert into #tbl(Amount)values(15000)
Aggregate Functions
1.
AVG () - Returns the average of the values in a group.
select AVG(Amount) from #tbl
Results
8000.00
How
it’s calculated?
Count:
15 (Rows)
Sum:
120000 (Amount Sum)
Average:
120000 / 15 = 8000
2.
Count () - Returns the number of items/rows in a group.
select count(Amount) from #tbl
Results
15
3.
Max () - Returns the maximum value in the expression.
select Max(Amount) from #tbl
Results
15000.00
4.
Min () - Returns the minimum value in the expression.
select Min(Amount) from #tbl
Results
1000.00
5.
Sum () - Returns the sum of all the values, or only the DISTINCT values, in the
expression.
select Sum(Amount) from #tbl
Results
120000.00
Date and Time Functions
1.
Current_Timestamp - Returns the current date and time.
select Current_Timestamp
Results
2015-08-03 12:38:33.720
2.
Dateadd () - Returns a new datetime value based on adding an interval to the
specified date.
select GETDATE() -- Today's date
Results
2015-08-03 12:56:35.630
select DATEADD(DD,1,GETDATE()) -- Add a date in today’s
date
Results
2015-08-04 12:56:35.630
select DATEADD(DD,-1,GETDATE()) -- Reduce a date in today’s
date
Results
2015-08-02 12:56:35.630
select DATEADD(MM,1,GETDATE()) -- Add a month in today’s
date
Results
2015-09-03 12:56:35.630
select DATEADD(MM,-1,GETDATE()) -- Reduce a month in
today’s date
Results
2015-07-03 12:56:35.630
select DATEADD(YY,1,GETDATE()) -- Add a year in today’s
date
Results
2016-08-03 12:56:35.630
select DATEADD(YY,-1,GETDATE()) -- Reduce a year in today’s
date
Results
2014-08-03 12:56:35.630
3.
Datediff () - Returns the number of date and time boundaries crossed between
two specified date.
select DATEDIFF(DD,'2015-04-01','2015-04-30') -- Return Date difference
between two date
Results
29
select DATEDIFF(MM,'2015-04-01','2015-06-30') -- Return Month difference
between two date
Results
2
select DATEDIFF(YY,'2013-04-01','2015-03-31') -- Return Year difference
between two date
Results
2
4.
Datename () - Returns a character string representing the specified datepart of
the specified date.
select GETDATE() -- Today's date
Results
2015-08-03 12:56:35.630
select DATENAME(DD,GETDATE()) -- Return Today's date
Results
3
select DATENAME(MM,GETDATE()) -- Return Current Month
Name
Results
August
select DATENAME(YY,GETDATE()) -- Return Current Year
Results
2015
select DATENAME(DW,GETDATE()) -- Return Today's Days
Results
Monday
5.
Datepart () - Returns an integer representing the specified datepart of the
specified date.
select GETDATE() -- Return Today's date
Results
2015-08-03 12:56:35.630
select DATEPART(DD,GETDATE()) -- Return Date
Results
3
select DATEPART(MM,GETDATE()) -- Return Month
Results
8
select DATEPART(YY,GETDATE()) -- Return Year
Results
2015
select DATEPART(DW,GETDATE()) -- Return Weekdays
Results
2
6.
Day () - Returns an integer representing the day datepart of the specified
date.
select GETDATE() -- Return Today's date
Results
2015-08-03 12:56:35.630
select DAY(GETDATE()) -- -- Return Date
Results
3
7.
Getdate () - Returns the current system date and time.
select GETDATE() -- Return Today's date
Results
2015-08-03 12:56:35.630
8.
Isdate () - Determines whether an input expression is a valid date.
select ISDATE('2015-04-01') -- Return 1 for correct
date.
Results
1
select ISDATE('2015-04-31') -- Return 0 for invalid
date.(In April date is not 31)
Results
0
9.
Month () - Return an integer that represents the month part of a specified
date.
select GETDATE() -- Return Today's date
Results
2015-08-03 12:56:35.630
select MONTH(GETDATE()) -- Return Month
Results
8
10.
Year () - Return an integer that represents the year part of a specified date.
select GETDATE() -- Return Today's date
Results
2015-08-03 12:56:35.630
select YEAR(GETDATE()) -- Return Year
Results
2015
String Functions
1.
Ascii () - Returns the ASCII code value of the leftmost character of a
character expression.
select Ascii('A')
Results
65
2.
Char () - Converts an integer ASCII code to a character.
select Char(65)
Results
A
3.
Charindex () - Returns the starting position of the specified expression in a
character string.
select Charindex(',','A,B,C',1)
Results
2
How
It's Working?
I
am searching comma (,) in a specified character string i.e. 'A, B, C'
And
starting posting of 1 means search starts from position 1
4.
Left () - Returns the Left-most position of the specified expression in a
character string.
select LEFT('ABCD',2)
Results
AB
5.
Len () - Returns the number of characters in the given string expression.
select LEN('ABCD')
Results
4
6.
Lower () - Returns a character expression with uppercase characters converted
to lowercase.
select LOWER('ABCD')
Results
abcd
7.
Ltrim () - Returns a character expression after removing leading blanks.
select ' ABCD'
Results
ABCD
select LTRIM(' ABCD')
Results
ABCD
8.
Quotename () - Returns a Unicode string with the delimiters added to make a
valid SQL Server delimited identifier.
select Quotename('ABCD')
Results
[ABCD]
9.
Replace () - Replaces all occurrences of the second expression with a third
expression.
select REPLACE('ABCD','CD','EF')
Results
ABEF
How
It's Working?
I
am replacing the 'CD' i.e. second expression with 'EF' i.e. third expression
10.
Replicate () - Repeats a character expression a specified number of times.
select REPLICATE('ABCD ',5)
Results
ABCD
ABCD ABCD ABCD ABCD
11.
Reverse () - Returns the reverse of a character expression.
select REVERSE('ABCD')
Results
DCBA
12.
Right () - Returns the specified number of character from the right part of a
character expression.
select RIGHT('ABCD',2)
Results
CD
13.
Rtrim () - Returns a character expression after truncating all trailing blanks.
select 'ABCD '
Results
ABCD
select RTRIM('ABCD ')
Results
ABCD
14.
Space () - Returns a string of repeated space characters.
select SPACE(10) + 'ABCD'
Results
ABCD
15.
Substring () - Returns part of character, binary, text, or image expression.
select SUBSTRING('ABCD',3,4)
Results
CD
How
It's Working?
Search
start from 3rd position of character string and end with 4th position of
character string
16.
Unicode () - Returns a Unicode integer value of the first character of the
expression.
select UNICODE('A')
Results
65
select UNICODE('ABCD')
Results
65
17.
Upper () - Returns a character expression with lowercase characters converted
to uppercase.
select UPPER('abcd')
Results
ABCD
Other Function
1.
App_Name () - Returns the application name for the current session.
select App_Name()
Results
Microsoft SQL Server Management
Studio - Query
2.
Cast () - Explicitly converts an expression of one data type to another.
select Cast(GETDATE() as VARCHAR) -- Casting the datetime
data type to varchar data type
Results
Aug 5
2015 12:50PM
3.
Coalesce () - Returns the first not Null expression among its arguments.
select * from #tbl
Using Coalesce function
select
Row_No,
Name,
COALESCE(HomePhoneNo,PersonalPhoneNo)ContactNo,
Amount
from #tbl
Using Case statement
select
Row_No,
Name,
case when HomePhoneNo is not null then HomePhoneNo else PersonalPhoneNo end as ContactNo,
Amount
from #tbl
Note:-
COALESCE function is the same as the CASE statement
4. Convert () - Explicitly converts an expression of one data type
to another.
select CONVERT(VARCHAR(20),GETDATE()) -- Converting the datetime data type to varchar data type
Results
Aug 5 2015 12:54PM
select CONVERT(VARCHAR(20),GETDATE(),0) -- Aug 5 2015 12:55PM
select CONVERT(VARCHAR(20),GETDATE(),1) -- 08/05/15
select CONVERT(VARCHAR(20),GETDATE(),2) -- 15.08.05
select CONVERT(VARCHAR(20),GETDATE(),3) -- 05/08/15
select CONVERT(VARCHAR(20),GETDATE(),4) -- 05.08.15
select CONVERT(VARCHAR(20),GETDATE(),5) -- 05-08-15
select CONVERT(VARCHAR(20),GETDATE(),6) -- 05 Aug 15
select CONVERT(VARCHAR(20),GETDATE(),7) -- Aug 05, 15
select CONVERT(VARCHAR(20),GETDATE(),8) -- 12:56:34
select CONVERT(VARCHAR(20),GETDATE(),9) -- Aug 5 2015 12:56:40
select CONVERT(VARCHAR(20),GETDATE(),10) -- 08-05-15
select CONVERT(VARCHAR(20),GETDATE(),11) -- 15/08/05
select CONVERT(VARCHAR(20),GETDATE(),12) -- 150805
select CONVERT(VARCHAR(20),GETDATE(),13) -- 05 Aug 2015 12:57:14
select CONVERT(VARCHAR(20),GETDATE(),14) -- 12:57:20:903
select CONVERT(VARCHAR(20),GETDATE(),100) -- Aug 5 2015 12:58PM
select CONVERT(VARCHAR(20),GETDATE(),101) -- 08/05/2015
select CONVERT(VARCHAR(20),GETDATE(),102) -- 2015.08.05
select CONVERT(VARCHAR(20),GETDATE(),103) -- 05/08/2015
select CONVERT(VARCHAR(20),GETDATE(),104) -- 05.08.2015
select CONVERT(VARCHAR(20),GETDATE(),105) -- 05-08-2015
select CONVERT(VARCHAR(20),GETDATE(),106) -- 05 Aug 2015
select CONVERT(VARCHAR(20),GETDATE(),107) -- Aug 05, 2015
select CONVERT(VARCHAR(20),GETDATE(),108) -- 12:59:04
select CONVERT(VARCHAR(20),GETDATE(),109) -- Aug 5 2015 12:59:19
select CONVERT(VARCHAR(20),GETDATE(),110) -- 08-05-2015
select CONVERT(VARCHAR(20),GETDATE(),111) -- 2015/08/05
select CONVERT(VARCHAR(20),GETDATE(),112) -- 20150805
select CONVERT(VARCHAR(20),GETDATE(),113) -- 05 Aug 2015 12:59:43
select CONVERT(VARCHAR(20),GETDATE(),114) -- 12:59:43:187
Note:-
CONVERT function can be used to display date/time data in
different formats.
5. Isnull () - Replaces Null with the specified replacement value.
select
Row_No,
Name,
ISNULL(HomePhoneNo,'')HomePhoneNo,
ISNULL(PersonalPhoneNo,'')PersonalPhoneNo,
Amount
from #tbl
Note:-
I
am replacing the Null value with blank space
6.
Isnumeric () - Determines whether an expression is a valid numeric type.
select ISNUMERIC('A')
Results
0
select ISNUMERIC('1')
Results
1
Note:-
0
- Means It’s not a numeric value
1
- Means It’s a numeric value
7.
Newid () - Create a unique value of type unique identifier.
select Newid()
Results
6047A8F0-C1DA-4069-B4D3-D5EE0B86C226
Note:-
Execution
Result is change each time.
8.
Nullif () - Returns a null value if the two specified expressions are
equivalent.
select Nullif('A','A') -- Returns NULL because values are the same
Results
NULL
select Nullif('A','B') -- Returns first value i.e. 'A' because values are
different
Results
A
Note:-
1.
NULLIF function compares both the value. If both the value is equal, the NULLIF
function returns NULL.
2.
If both the value is not same, its return first value which is 'A'
9. @@Rowcount - Returns the number of rows affected by the last
statement.
select * from #tbl
select @@Rowcount
No comments:
Post a Comment