Archive

Archive for the ‘TSQL’ Category

SQL Server Denali – FORMAT() string function

02/09/2011 Leave a comment

SQL Server Denali brings us new string function, FORMAT function. It will make formatting of date/time and number values easier.

Syntax: FORMAT (value, format [,culture])

Formatting Date/Time

Let’s see how it works:

DECLARE @a datetime = getdate()
-- If the culture argument is not provided,
-- then the language of the current session is used.
SELECT FORMAT(@a, N'd') AS d,
FORMAT(@a, N'dd') AS dd,
FORMAT(@a, N'ddd') AS ddd,
FORMAT(@a, N'dddd') AS dddd

SELECT FORMAT(@a, N'M') AS M,
FORMAT(@a, N'MM') AS MM,
FORMAT(@a, N'MMM') AS MMM,
FORMAT(@a, N'MMMM') AS MMMM

SELECT FORMAT(@a, N'y') AS y,
FORMAT(@a, N'yy') AS yy,
FORMAT(@a, N'yyy') AS yyy

If you specify culture argument, you can easily format date to other languages. Next examples show you how to format date, weekday and month to Croatian language.

-- Formatting with Croatian language
SELECT FORMAT(@a, N'd', N'hr-HR') AS d,
FORMAT(@a, N'dd', N'hr-HR') AS dd,
FORMAT(@a, N'ddd', N'hr-HR') AS ddd,
FORMAT(@a, N'dddd', N'hr-HR') AS dddd

SELECT FORMAT(@a, 'M','hr-HR') AS M,
FORMAT(@a, 'MM','hr-HR') AS MM,
FORMAT(@a, 'MMM','hr-HR') AS MMM,
FORMAT(@a, 'MMMM','hr-HR') AS MMMM

Examples with German and Italian languages.

-- Formatting with German language
SELECT FORMAT(@a, N'd', N'de-DE') AS d,
FORMAT(@a, N'dd', N'de-DE') AS dd,
FORMAT(@a, N'ddd', N'de-DE') AS ddd,
FORMAT(@a, N'dddd', N'de-DE') AS dddd

-- Formatting with Italian language
SELECT FORMAT(@a, N'M', N'it-IT') AS M,
FORMAT(@a, N'MM', N'it-IT') AS MM,
FORMAT(@a, N'MMM', N'it-IT') AS MMM,
FORMAT(@a, N'MMMM', N'it-IT') AS MMMM

List of all available languages you can find in National Language Support (NLS) API Reference. With FORMAT function you can also use custom formatting. Let’s see how custom formatting works.

DECLARE @a datetime = getdate()

-- Custom formatting of date
SELECT FORMAT ( @a, N'dd/MM/yyyy') AS 'dd/MM/yyyy',
FORMAT ( @a, N'dd.MM.yyy')  AS 'dd.MM.yyy',
FORMAT ( @a, N'dd.MMMM.yy', 'hr-HR') AS 'dd.MMMM.yy Croatian',
FORMAT ( @a, N'dd.MMMM.yyy (dddd)', 'hr-HR') AS 'dd.MMMM.yyy (dddd) Croatian',
FORMAT ( @a, N'dddd, dd.MMM.yy', 'hr-HR') AS 'dddd, dd.MMM.yy Croatian',
FORMAT ( @a, N'dddd MMMM dd, yyyy', 'en-US') AS 'MMMM dd, yyyy (dddd) English'

-- Custom formatting of time
SELECT FORMAT ( @a, N'hh:mm:ss') AS 'hh:mm:ss',
FORMAT ( @a, N'hh:mm') AS 'hh:mm',
FORMAT ( @a, N'hh') AS 'hh',
FORMAT ( @a, N'mm') AS 'mm',
FORMAT ( @a, N'ss') AS 'ss'

Formatting Number

FORMAT function is very useful if you need to format currency value. Here are some examples for formatting currency value.

DECLARE @a numeric(10,2)
SET @a = '5132.45'
SELECT FORMAT(@a, N'c') AS SessionLanguage,
FORMAT(@a, N'c', 'hr-Hr') AS Croatian,
FORMAT(@a, N'c', 'de-DE') AS German,
FORMAT(@a, N'c', 'it-IT') AS Italian

-- You can also control number of digits after the decimal point
SELECT FORMAT(@a, N'c0', 'de-DE') AS German0,
FORMAT(@a, N'c1', 'de-DE') AS German1,
FORMAT(@a, N'c2', 'de-DE') AS German2,
FORMAT(@a, N'c3', 'de-DE') AS German3

With FORMAT function you can even format value to scientific and hexadecimal.

DECLARE @I int = 90
SELECT FORMAT(@I,'e') AS Scientific,
FORMAT(@I,'x') AS Hexa,
FORMAT(@I,'x4') AS Hexa1

Format function is nice addition to the T-SQL language and it will make formatting much easier. Various tricks with CAST and CONVERT functions will no longer be required.

Categories: Denali, String Functions, TSQL

SQL Server Denali – CONCAT() string function

28/08/2011 Leave a comment

CONCAT() is new string function in SQL Server Denali. It returns a string that is the result of concatenating two or more string values (or values that can be converted to string).
Syntax: CONCAT (string_value1, string_value2 [, string_valueN ] )

With CONCAT function, you can concatenate between 2 and 254 values. If you try to use CONCAT with only one value or with more than 254 values you will get an error.

SELECT CONCAT('SQL Server') AS Result

Msg 189, Level 15, State 1, Line 1
The concat function requires 2 to 254 arguments.

Simple examples:

SELECT CONCAT('SQL', 'Server', 'Denali') AS Result1
/*
Result1
---------------
SQLServerDenali
*/

DECLARE @a varchar(10) = 'SQL'
DECLARE @b varchar(10) = 'Server'
DECLARE @c varchar(10) = 'Denali'
SELECT CONCAT(@a, @b, @c) AS Result2
/*
Result2
------------------------------
SQLServerDenali
*/

USE AdventureWorks2008R2
GO
SELECT TOP 5 CONCAT(FirstName, LastName) AS FullName
FROM Person.Person
/*
FullName
----------------------
SyedAbbas
CatherineAbel
KimAbercrombie
KimAbercrombie
KimAbercrombie
*/

It’s easy to work with CONCAT function, but as you can see there is no string delimiter between values. To get proper FullName in the last example you will need to add space between FirstName and LastName.

USE AdventureWorks2008R2
GO
SELECT TOP 5 CONCAT(FirstName, ' ', LastName) AS FullName
FROM Person.Person
/*
FullName
------------------------
Syed Abbas
Catherine Abel
Kim Abercrombie
Kim Abercrombie
Kim Abercrombie
*/

CONCAT function has 2 advantages compared to the concatenating of strings using the “+” operator:
1. NULL values are implicitly converted to an empty string
2. All arguments are implicitly converted to string

CONCAT() and NULL arguments

CONCAT function automatically converts all NULL arguments to an empty string. It’s great for T-SQL developers. In next examples you can see the difference between CONCAT function and concatenation of strings using the “+” operator.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = NULL
DECLARE @c varchar(10) = 'Denali'
-- CONCAT implicitly convert NULL to an empty string
SELECT CONCAT(@a, @b, @c) AS Result1

-- If you use "+" operator with NULL result is NULL
SELECT @a + @b + @c AS Result2

-- To get the same result with "+" operator you need to deal with NULL values
SELECT @a + ISNULL(@b, '') + @c AS Result3
/*
Result1
------------------------------
SQL ServerDenali
Result2
------------------------------
NULL
Result3
------------------------------
SQL ServerDenali
*/

If all the arguments are null, then an empty string of type varchar(1) is returned.

CONCAT() and non-string arguments

CONCAT function implicitly converts all non-string arguments to string data types (if conversion is possible) and then concatenate values.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT CONCAT(@a, @b, @c, @d) AS Result1
/*
Result1
------------------------------------------
SQL ServerDenali2011Aug 27 2011  9:42PM
*/

As you can see, @c and @d are implicitly converted to string and you get result back. If you use same example with “+” operator you will get an error.
Msg 245, Level 16, State 1, Line 5
Conversion failed when converting the varchar value ‘SQL ServerDenali’ to data type int.

To get the same result with “+” operator you need to convert @c and ©d values to string data types.

DECLARE @a varchar(10) = 'SQL Server'
DECLARE @b varchar(10) = 'Denali'
DECLARE @c int = 2011
DECLARE @d datetime = getdate()
SELECT @a + @b + CONVERT(varchar(10),@c) + CONVERT(varchar(20),@d) AS Result2
/*
Result2
--------------------------------------------------
SQL ServerDenali2011Aug 27 2011  9:42PM
*/

As you can see, concatenating string is much easier with CONCAT function.  What you think?

Categories: Denali, TSQL

SQL Server Denali – IIF() and CHOOSE() functions

22/08/2011 Leave a comment

SQL Server Denali introduced 2 new logical functions: IIF and CHOOSE.

IIF function

IIF function returns one of two values, depending on whether Boolean expression evaluates to true or false.
Syntax:  IIF (boolean_expression, true_value, false_value)
IIF function is simple replacement for:
Case when <boolean_expression>=true  then <true_value> else <false_value> end

Let’s see how IIF function works.

-- Simple IIF
DECLARE @a int = 45;
DECLARE @b int = 40;
SELECT IIF ( @a > @b, 'TRUE', 'FALSE' ) AS Result;
/*
Result
------
TRUE
*/

In next select statement, I will use IIF function to retrieve PriceDescription value. If ListPrice is greater the 1000 then set Expensive for PriceDscription else it is Cheap.

If we look at the execution plan, we will see that Compute Scalar is used to support IIF function. On Compute Scalar property we can see that IIF function is converted to CASE expression. So, the same rules that apply to the CASE statement also apply to IIF function.

IIF function can be nested but only up to the level of 10. Next examples show IIF nesting to 10th level and error if we try to nest IIF up to the level of 11.

CHOOSE function

CHOOSE function returns a value from the given array based on the specified index position.

Syntax: CHOOSE (index, val_1, val_2 [, val_n ])

Let’s see how CHOOSE function works.

SELECT CHOOSE(1, 'a', 'b', 'c') AS First,
CHOOSE(2, 'a', 'b', 'c') AS Second;
/*
First Second
----- ------
a     b
*/
-- If the index value exceeds the bounds of the array of values,
-- then CHOOSE returns null.
SELECT CHOOSE(0, 'a', 'b', 'c') AS First,
CHOOSE(4, 'a', 'b', 'c') AS Second
/*
First Second
----- ------
NULL  NULL
*/
-- If the provided index value has a numeric data type other than int,
-- then the value is implicitly converted to an integer
SELECT CHOOSE(2.5, 'a', 'b', 'c') AS First,
CHOOSE(3.9, 'a', 'b', 'c') AS Second;
/*
First Second
----- ------
b     c
*/

In next query, I will show how CHOOSE function can be used in clauses such as select list, GROUP BY and ORDER BY.

SELECT p.Name,
SUM(s.OrderQty) SellQuantity,
CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th') AS Quarter
FROM Sales.SalesOrderDetail s
INNER JOIN Production.Product p
ON p.ProductID = s.ProductID
WHERE s.ModifiedDate >= '2006-01-01'
AND s.ModifiedDate < ' 2007-01-01'
GROUP BY p.Name,CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')
HAVING SUM(s.OrderQty) > 600
ORDER BY p.Name, CHOOSE(DATEPART(QUARTER, s.ModifiedDate), '1st', '2nd', '3rd', '4th')

If we look at the execution plan, we will see that Compute Scalar is used to support CHOOSE function. The engine has taken the CHOOSE function and converted it into a CASE.

IIF and CHOOSE functions are nice additions to the language and it’s good to know how they work.

Categories: Denali, TSQL

SQL Server Denali – EOMONTH()

03/08/2011 1 comment

SQL Server Denali introduces 7 new date and time built-in functions. One of these functions is EOMONTH(). EOMONTH() returns the date value that represents the last day of the month for given date.

Syntax: EOMONTH( start_date [, months_to_add] )

Pre- Denali, to calculate the date value that represents the last day of the month we used:

SELECT DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0))
/*
-----------------------
2011-08-31 00:00:00.000
*/

In Denali, we can use EOMONTH function.

SELECT EOMONTH (GETDATE())LastDay1,
EOMONTH('2011-09-05') AS LastDay2
/*
LastDay1                LastDay2
----------------------- ----------------------
2011-08-31 00:00:00.000 2011-09-30 00:00:00.00
*/

If we specify “months_to_add” argument, then EOMONTH adds the specified number of months to start_date, and then returns the last day of the month for the resulting date.

SELECT EOMONTH(GETDATE(),-1) AS LastDayOfPreviousMonth,
EOMONTH(GETDATE()) AS LastDayOfMonth,
EOMONTH (GETDATE(),1)LastDayOfNextMonth
/*
LastDayOfPreviousMonth  LastDayOfMonth         LastDayOfNextMonth
----------------------- ----------------------- -----------------------
2011-07-31 00:00:00.000 2011-08-31 00:00:00.000 2011-09-30 00:00:00.000
*/

It is strange that SQL Server team hasn’t added a function for beginning of the month.  For me, BOMONTH would be more valuable because almost all reporting queries I’m writing make more sense with first date of month.

To calculate the first day of the month for given date.

-- Pre-Denali
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)
-- With EOMONTH function
SELECT DATEADD(DAY,1,EOMONTH (GETDATE(),-1))
-- or with DATETIMEFROMPARTS - new Denali datetime function
SELECT DATETIMEFROMPARTS (YEAR(GETDATE()),MONTH(GETDATE()),1,0,0,0,0)
Categories: Denali, TSQL

SQL Server Denali– TRY_CONVERT() conversion function

01/08/2011 Leave a comment

TRY_CONVERT is new conversion function in SQL Server Denali. Unlike the CAST and CONVERT functions, TRY_CONVERT allows us to test whether it is possible to convert a value to specific data type.

TRY_CONVERT converts passed value to the specified data type, if conversion isn’t possible null is returned. But, if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Syntax: TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )
style accepts the same values as the style parameter of the CONVERT function.

Let’s see how it works.

SELECT TRY_CONVERT(datetime, '2010-12-13') AS Date1,
TRY_CONVERT(datetime2, '20101203') AS Date2,
TRY_CONVERT(datetime, '21:15') AS  Date3,
TRY_CONVERT(time, '2010-12-13') AS Time1,
TRY_CONVERT(time, '21:15') AS Time2

DECLARE @a as datetime
SET @a = '2011-07-25 21:15:31.120'

SELECT TRY_CONVERT(date, @a),
TRY_CONVERT(time, @a),
TRY_CONVERT(varchar(10), @a,104),
TRY_CONVERT(varchar(10), @a,108),
TRY_CONVERT(numeric(18,6), @a),
TRY_CONVERT(float, @a),
TRY_CONVERT(datetime,TRY_CONVERT(float, @a))

Pre-Denali, conversion of value ‘2010-12-32’ to datetime will raise an error.

SELECT CONVERT(datetime, '2010-12-32')

Msg 242, Level 16, State 3, Line 1
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

In Denali, with TRY_CONVERT we get NULL. But if we try a conversion that is explicitly not permitted, TRY_CONVERT fails with an error.

DECLARE @a XML
SET @a = '<a>1</a>'
SELECT TRY_CONVERT(int, @a)

Msg 529, Level 16, State 2, Line 3
Explicit conversion from data type xml to int is not allowed.

To see how TRY_CONVERT works with numbers, I will create table, insert some values and try to convert values from the table to a various number data types.

CREATE TABLE #T1 (a varchar(20))
GO
INSERT INTO #T1
VALUES ('10'),('12.4'),('Value'),('12,6'),('-5'),('3d3'),(CHAR(9))/*Tab*/
,(CHAR(10))/*new line*/,('+'),('-'),('$'),(CHAR(32))/*space*/,('2.31e-3')
GO
SELECT a,
TRY_CONVERT(int, a) AS ASInteger,
TRY_CONVERT(numeric(8,2), a) AS ASNumeric,
TRY_CONVERT(float, a) AS ASFloat,
TRY_CONVERT(money, a) AS ASMoney,
FROM #T1

As you can see, if we try to convert signs “+” ,“-“ and space to integer we will get 0, but they can’t be converted to numeric data type. We can easily find all integer values in the table.

-- Everything that can be parsed to int
SELECT a,
TRY_CONVERT(int,a) IntegerValues
FROM #t1
WHERE TRY_CONVERT(int,a) IS NOT NULL

-- without signs "+","-" and space
SELECT a,
TRY_CONVERT(int,a) IntegerValues
FROM #t1
WHERE TRY_CONVERT(int,a) IS NOT NULL
AND a NOT IN ('+','-',CHAR(32))

TRY_CONVERT is excellent for data type conversion and will eliminate a lot of issue. No more need for custom IsInt or IsNumeric function.

Categories: Denali, TSQL

SQL Server Denali– PARSE() and TRY_PARSE() conversion functions

30/07/2011 Leave a comment

SQL Server Denali introduced new conversion functions: PARSE() and TRY_PARSE().

PARSE() function

PARSE() function converts expression (string value) to date/time and number data types if conversion is possible. If conversion isn’t possible it raises an error.

-- Parse to datetime specifying culture in which string value is formatted
SELECT PARSE('Monday, 13 December 2010' AS datetime USING 'en-US') AS Result
SELECT PARSE('28. srpnja 2011. četvrtak' AS datetime USING 'hr-HR') AS Result

Result
———————–
2010-12-13 00:00:00.000
Result
———————–
2011-07-28 00:00:00.000

-- If the culture argument isn't provided, the language of current session is used.
SELECT PARSE('Monday, 13 December 2010' AS datetime) AS Result

Result
———————–
2010-12-13 00:00:00.000

-- Language for this session is English:
SELECT PARSE('28. srpnja 2011. četvrtak' AS datetime) AS Result

It raise an error:
Msg 9819, Level 16, State 1, Line 1
Error converting string value ’28. srpnja 2011. četvrtak’ into data type datetime using culture ”.

Language for session is set either implicitly or explicitly by using SET LANGUAGE statement.

SET LANGUAGE Croatian
SELECT PARSE('28. srpnja 2011. četvrtak' AS datetime) AS Result

Result
———————–
2011-07-28 00:00:00.000

-- Parse with currency symbol
SELECT PARSE('$345.56' AS money USING 'en-US') AS Result
SELECT PARSE('345,56 kn' AS money USING 'hr-HR') AS Result

Result
———————
345,56
Result
———————
345,56

-- Parse to numeric data type
SELECT PARSE('345,58' AS numeric(8,1) USING 'hr-HR') AS Result
SELECT PARSE('345,58' AS numeric(8,2) USING 'hr-HR') AS Result

Result
—————————————
345.6
Result
—————————————
345.58

TRY_PARSE() function

TRY_PARSE() function converts expression (string value) to date/time and number data types or return NULL if conversion isn’t possible.

SELECT TRY_PARSE('$345.56' AS money USING 'hr-HR') AS Result
SELECT TRY_PARSE('$345.56' AS money USING 'en-US') AS Result

First value returns NULL, since ‘$’ is not a valid currency symbol according to Croatian language.

Result
———————
NULL
Result
———————
345,56

DECLARE @Table table (Datum varchar(100))
INSERT INTO @Table
VALUES('Monday, 13 December 2010'),('28. srpnja 2011. četvrtak')
SELECT Datum FROM @Table
/*
Datum
-------------------------
Monday, 13 December 2010
28. srpnja 2011. četvrtak
*/

-- Convert to datetime with English culture
SELECT TRY_PARSE(Datum AS datetime USING 'en-US') AS Datum
FROM @Table

-- Second value returns NULL, since '28. srpnja 2011. četvrtak' is
-- not a valid datetime formatting according to us_english
/*
Datum
-----------------------
2010-12-13 00:00:00.000
NULL
*/

-- Convert to datetime with English or Croatian language
SELECT
CASE WHEN TRY_PARSE(Datum AS datetime USING 'en-US') IS NULL
THEN PARSE(Datum AS datetime USING 'hr-HR')
ELSE PARSE(Datum AS datetime USING 'en-US')
END AS Datum
FROM @Table

/*
Datum
-----------------------
2010-12-13 00:00:00.000
2011-07-28 00:00:00.000
*/

Categories: Denali, TSQL