56 T-SQL Functions

This article demo some of the common T-SQL Functions.  Purple are new to SQL 2012 which will be cover in here.

• NULL Functions
• ISNULL
• COALESCE
• Date/Time Functions
• GETDATE()
• GETUTCDATE()
• DATEPART()
• DATEDIFF()
• DATEADD()
• ISDATE()
• DATEROMPARTS()
• TIMEFORPARTS()
• EOMONTH()
• PARSE()
• String Functions
• CHARINDEX()
• PATINDEX()
• LEFT()
• RIGHT()
• LTRIM()
• RTRIM()
• LEN()
• CONCAT()
• FORMAT()
• Data Type Conversions
• CONVERT()
• CAST()
• TRY_PARSE()
• TRY_CONVERT()
• Logical Functions
• CHOOSE()
• IIF()

NULL FUNCTIONs

Please see here.

Date/Time Functions

Use [AdventureWorks2014]

GO

-- DATE Related functions

SELECT GETDATE()

GO

SELECT GETUTCDATE()

GO

SELECT 

    DATEPART(year, GETDATE() ) AS 'YEAR',

    DATEPART(month,  GETDATE()) AS 'MONTH',

    DATEPART(day,  GETDATE()) AS 'DAY',

    DATEPART(dayofyear,  GETDATE()) 'DAT OF THE YEAR',

    DATEPART(weekday,  GETDATE()) AS 'WEEK DAY'

GO

SELECT DATEDIFF(day, '2010/6/11', GETDATE());

GO

SELECT DATEADD(month, 1, GETDATE());

GO

IF ISDATE('2009-05-12 10:19:41.177') = 1

    PRINT 'VALID'

ELSE

    PRINT 'INVALID';

GO

IF ISDATE('I AM STRING') = 1

    PRINT 'VALID'

ELSE

    PRINT 'INVALID';

ISDATE() is used to validate whether input string is valid date or not.

String Functions

• CHARINDEX(): Returns the starting position of the first occurrence of a string in a specified expression, or zeros if the string is not found, on all valid text and character data types.
• PATINDEX(): Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
• LEFT(): Returns the left part of a character string with the specified number of characters.
• RIGHT(): Returns the right part of a character string with the specified number of characters.
• LTRIM(): Remove the leading blank
• RTRIM(): Remove the blank in the end of string
• LEN(): show the length of the sting

DECLARE @document varchar(64);

SELECT @document = 'Happy Birthday everyone.';

SELECT CHARINDEX('Birthday', @document);

SELECT PATINDEX('%Bir%', @document);

SELECT LEFT(@document, 6)

SELECT RIGHT(@document, 6)

SELECT LTRIM(@document)

SELECT RTRIM(@document)

SELECT LEN(@document)

GO

Data Type Conversions

• CONVERT()
• CAST()
• PARSE()

SELECT CONVERT(datetime2,'12/31/2010') AS Result;

GO

SELECT CAST('12/31/2010' AS datetime2) AS Result;

GO

SELECT PARSE('12/31/2010' AS datetime2 USING 'en-US') AS Result;

GO

For CONVERT, CAST and PARSE, if converting data type fail, it will generate the error message. In SQL 2012, new functions TRY_CONVERT(), TRY_PARSE() has been introduce, if convention fail, it will return NULL .