Function in SQL server
Predefined function
Date time: Data type:-time, date, small date time, date time, datetime22, date time off set
function description |
getdate() commonly used |
current_timestamp ansi sql equivalent to getdate() |
sysdatetime() more fractional second precision |
sysdatetimeoffset() more fractional second precision |
+offset |
getutcdate() utc date and time |
sysutcdatetime() utc date time with more fractional |
second |
· IS DATE():- checks if the given value is valid date, time, date time.return 1 for success, o for un success.
· Day(getdate()):-day function return day of month
· Month(getdate())
· year(getdate()) we can put '01/31/2017'
· DateName(two parameter)it will return nvarchar.
· DateName(day,getdate)
· DateName(month,getdate)
· DateName(year,getdate)
· DateName(weekday,getdate)
· DatePart(two parameter) it is samme as date name but it will return integer.
· DateAdd(datepart,no to add,date)
date part:-day,weekday,month,year
no to add:-may be +ve or -ve
DateDiff(datepart,startdate,enddate)(heredate part mean month,year,day)
Cast and convert:-
Cast(expression as datatype(length)) length is a optional.
Convert(datatype(length),expression,style) style parameter is like(101 to 105), and it is optional
Convert is more flexible than cast() because it is possible to control how we want datatype to be converted using style with convert function.
Note: cast is based on ANSI standard and convert is specific to sql server.
Mathematical function in sql server.
ABS()
ceiling()
floor()
power(p1,p2)
square(number)
rand()
floor(rand())
Round (expression,length,optionalparameter)
expression:-number
optionalparameter:- 0,1
0:-it indicate rounding
1:-it indicate truncate
length +ve:-round count from rigth side of decimal
length -ve :-round count from left side of decimal
SELECT ROUND(125.315, 2);
Result: 125.320 (result is rounded because 3rd parameter is omitted)
SELECT ROUND(125.315, 2, 0);
Result: 125.320 (result is rounded because 3rd parameter is 0)
SELECT ROUND(125.315, 2, 1);
Result: 125.310 (result is truncated because 3rd parameter is non-zero)
SELECT ROUND(125.315, 1);
Result: 125.300 (result is rounded because 3rd parameter is omitted)
SELECT ROUND(125.315, 0);
Result: 125.000 (result is rounded because 3rd parameter is omitted)
SELECT ROUND(125.315, -1);
Result: 130.000 (result is rounded because 3rd parameter is omitted)
SELECT ROUND(125.315, -2);
Result: 100.000 (result is rounded because 3rd parameter is omitted)