2012 MS SQL TSQL Functions

-- Format very useful in formatting results

SELECT FORMAT(GETDATE(), 'MM/dd/yyyy'),

          FORMAT(1.0, 'C'),

          FORMAT(42, '###,##0.00')

-- turn text into a particular data type, if it can’t be converted then it fails

select parse('05/15/1968' AS datetime2),

              parse('$123.45' AS money)

-- same as parse but if it doesn’t convert it returns null

SELECT try_parse('05/15/1968' AS datetime2),

              try_parse('99/15/1968' AS datetime2)

-- evalues the first parameter, then returns the second if true, or the third if false.

SELECT IIF(1=1, 'true', 'false'),

       IIF(try_parse('99/15/1968' AS datetime2) is NULL, 'Was Null', 'Not Null')

-- indexed based, conversion if 1 then ‘one’, if 2 then ‘two’ etc.

SELECT CHOOSE(1, 'One', 'Two', 'Three')

-- concat function, not sure of usefulness yet maybe in conjunction with format to make some nice strings, but sadly no aggregate version

-- there are a couple funky rules about size of strings so read up before assuming.

SELECT CONCAT('first string', ' Second String')

-- return the end of month calendar date for any given date, can also use an offset, with offset it adds X months to the date then calculates the end of month

SELECT EOMONTH('5/15/1968'), EOMONTH('5/15/1968', 3)

-- create a date object from the individual values.

SELECT DATEFROMPARTS(1968, 5, 15),

              DATETIME2FROMPARTS(1968, 5, 15, 4, 0, 30, 0, 0)