-- 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)
