Tuesday, September 30, 2014

1 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';

image

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

image

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

image

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
image

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 .

1 comments:

  1. Great blog created by you. I read your blog, its best and useful information. You have done a great work. Super blogging and keep it up.php jobs in hyderabad.

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates