SQL Server Mastery----built-in functions from getting Started

--Supplement to aggregate functions--var to find the variance of a column--Mathematical Functions    Select ABS(-1)--Absolute Value    Select CEILING(3.5)    Select  Floor(3.5)    Select round(3.5555,2)        Select SQRT(4)    Select Square(2)    Select POWER(2,3)    Select RAND()* ---Date-time functions    Select Current_timestamp  --2015-09-09 22:53:12.727    Select GETDATE()--2015-09-09 22:55:02.980    SelectgetUTCDate ()--2015-09-09 14:55:54.867-World Standard Time        SELECT  Year(GETDATE())    SELECT MONTH(GETDATE())    SELECT  Day(GetDate())    SELECT Datename( Year,Getdate())--returns a string representing the specified date part of a specified date    SELECT Datename(WEEKDAY,Getdate())--Wednesday    --DATEADD (datepart, number, date) --returns a new datetime value that is added to the specified date after a time interval.  SELECT DATEADD(Week,1,GetDate())--date plus one week after the current date--DATEDIFF (datepart, StartDate, EndDate)--returns the number of date boundaries and time boundaries across two specified dates. SELECT DATEDIFF( Year,'2006-10-11','2006-11-01')--returns 0--conversion of date functions--CommonSelect CONVERT(varchar( -),GETDATE(), +)--2006-05-16 10:57:47.157Select CONVERT(varchar( -),GETDATE(), -)--2006-05-16 10:57:47.250Select CONVERT(varchar( -),GETDATE(), -)--2006-05-16 10:57:47Select CONVERT(varchar( -),GETDATE(), the)--20060516Select CONVERT(varchar( -),GETDATE(),121)--2006-05-16 10:57:49.700Select CONVERT(varchar( -),GETDATE(), -)--2006-05-16 10:57:49Select CONVERT(varchar( -),GETDATE(), at)--2006-05-16Select CONVERT(varchar( -),GETDATE(),102)--2006.05.16Select CONVERT(varchar( -),GETDATE(), -)--10:57:46:967Select CONVERT(varchar( -),GETDATE(), -)--10:57:47--String FunctionsSelect ASCII('a')--Convert to ASCII codeSelect Char( $)--convert ASCII integer to character ASelect  Left('ABCDEFG',3)--left-Most 3 string ABCSelect  Right('ABCDEFG',3)--right-most 3 stringsSelect Len('ABCDEFG')--number of returned strings 7Select Lower('AAaaa')--uppercase to lowercase aaaaaSelect Upper('AAaaa')--AAAAASelect Len(LTRIM('AAA a'))--Go left blank 5Select Len(RTRIM('AAA a'))--go to right space 7Select nchar( the)--return Unicode charactersSelect REPLACE('ABCDA','a','BBB')--Replace A with the BBB bbbbcdbbbSELECT STR(1234.56,6,1 )--counting starting from 1SELECT STUFF('abcdef',2,3,'IJKLMN')--AijklmnefSelect SUBSTRING('FSDFSD',2,2) SD----Type conversionSelect CAST('123'  as int)--123Select CONVERT(int,'123')--123Select CAST(123.4  as int)--123Select CONVERT(int,123.4)--123Select CAST('123.4'  as int)Select CONVERT(int,'123.4')--Conversion failed when converting the varchar value ' 123.4 ' to data type int.Select CAST('123.4'  as decimal)--123Select CONVERT(decimal,'123.4')--123Select CAST('123.4'  as decimal(9,2))--123.40Select CONVERT(decimal(9,2),'123.4')--123.40

SQL Server Mastery----built-in functions from getting Started