My gap in the void

Part of my new  SQL 2012  TSQL features series is a look at the new  F ORMAT  function.   This function can be used to format date/time and number values as strings. Almost all SQL developers will come across wanting to use SQL to format their dates in a specific way or make numbers more presentable. It has a very simple syntax and does pretty much exactly what you'd hope.  See the documents online for more usage examples.

Part of my new SQL 2012 TSQL features series is a look at the new CONCAT function. CONCAT ( string_value1, string_value2 [, string_valueN ] ) From the documents online: CONCAT takes a variable number of string arguments and concatenates them into a single string. It requires a minimum of two input values; otherwise, an error is raised. All arguments are implicitly converted to string types and then concatenated. Null values are implicitly converted to an empty string. If all the arguments are null, an empty string of type varchar(1) is returned. The implicit conversion to strings follows the existing rules for data type conversions.  This means it's now possible to easily concatenate strings without worrying about fields being null.  Previously if you used a field that was null when adding strings together it would have resulted in a total null string.  This meant using some custom logic to deal with the null, like the following. 'A' + case when [field]...

A useful way to group data in result sets using only one date field is often to employ a YYYY-MM or YYYY-MM-DD format. In the past I'd opted for a slightly painful way of taking the dateparts of the year and month, converting them to varchars and then concatinating them together with the hyphens. The following are examples use getdate(), but obviously you'd use your own field. SELECT CAST(DATEPART(YYYY, GETDATE()) as CHAR(4)) + '-' + REPLICATE('0', 2-LEN(CAST(DATEPART(M, GETDATE()) as VARCHAR(2)))) + CAST(DATEPART(M, GETDATE()) as VARCHAR(2)) Not very pretty I think you'll agree. Especially if you want to immediately use it in a group by. The following is much more elegant and returns the same result! SELECT CONVERT(CHAR(7),GETDATE(),23) If you want to include the day part as well then use all 10 characters returned: SELECT CONVERT(CHAR(10),GETDATE(),23) For more formats have a look at  http://msdn.microsoft.com/en-us/library/ms187928.aspx