A few neat SQL tricks

While reading Advanced Transact-SQL for SQL Server 2000 by Itzik Ben-Gan & Tom Moreau this week I came across a few tips about dealing with dates. Most of them, but not all, I had already figured out previously but I thought they'd be handy to note anyway.

Left Padding Numerics with Leading Zeros
How many times have you needed to present numeric data with a fixed length? If you cast an int to a string datatype you often get a bunch of trailing spaces. What if you convert the int to a fixed length string with STR(), thereby right-aligning the int data, and then change the leading spaces to zero characters with REPLACE(). Like this example to display the integer 52 as a five character fixed length string with leading zeros:

DECLARE @i int
SET @i = 52
SELECT REPLACE(STR(@i, 5, 0), ' ', '0')

Finding the First Day of the Month
The CONVERT() function will convert a datetime value into a string (among other uses for the function) and a 3rd style parameter can deal with many different date & time formats. Format 112 (yyyymmdd) is particularly useful with extracting date information from a datetime or smalldatetime value, and it's useful for finding the first day of a given month. The first 6 characters gets us the year & month and then all we need is to concatenate '01' to the end of that string as follows:

SELECT CAST(CONVERT(char(6),GETDATE(),112) + '01' as smalldatetime)

Finding the Last Day of the Month
This is really a variation on the previous theme. The last day of a given month is also equal to the day before the first day of the next month. So if we can get the first day of the next month, then we should be able to easily get the day before that (a.k.a. the last day of the month). We can use the DATEADD() function to add and subtract components of a date. So we get the first day in the given month (as above), add 1 month and then subtract 1 day as follows (the CAST() in the previous example is unnecessary because the varchar result from the CONVERT() function will be implicitly converted to a datetime datatype):

SELECT DATEADD(dd, -1, DATEADD(mm, 1, CONVERT(char(6),GETDATE(),112) + '01'))

Unambiguous Date Formats
This is not exactly a tip but since it's a question that regularly gets asked in the public sqlserver newsgroups (although usually indirectly and unintentionally) it's worth while noting. Many date formats are ambiguous. Formats such as 1/6/2005 or even 2005-06-01 are ambiguous; both can be interpreted as June 1, 2005 and Jan 6, 2006 depending on the locale. However, the ISO format (styles 12: yymmdd, and 112: yyyymmdd) is always unambiguous. So when converting dates for use in calculations you should always use the ISO format. Likewise, when embedding constant datetime values in code you should always use the yyyymmdd hh:mi:ss.msec format as it's always unambiguous. For example:

DECLARE @d datetime
SET @d = '20050601 11:35:00.000'
SELECT @d

I hope these little tips are as helpful to you as they have been to me.