Thursday, June 23, 2005

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'
I hope these little tips are as helpful to you as they have been to me.


At 12/6/06 08:57, Anonymous Hugo Kornelis said...

Hi Mike,

Nice post.

However, there are some additions I'd like to make.

For first day of the month, there's an alternative technique that doesn't need the conversion to string and back and is therefore somewhat faster:
SELECT DATEADD(month, DATEDIFF(month, '20000101', getdate()), '20000101')
(where '20000101' is a quite randomly chosen date - most any date will do).
This is somehwat harder to grasp than your technique, but it has the advantage of being easy to modify for other queries.

For instance, to get the last day of the month we only need to change the second occurence of Jan 1st, 2000 to Jan 31st, 2000:
SELECT DATEADD(month, DATEDIFF(month, '20000101', getdate()), '20000131')

Finally, about the date format. You are right about the format for date only, but for date and time, the format has to change to the ISO 8601 defined format: yyyy-mm-ddThh:mm:ss.mmm (note the required interpunction: dashes in the date, colons in the time, an uppercase T in the middle, and a dot between the seconds and the milliseconds - the milliseonds are optional BTW, so '2005-06-01T11:35:00' would be a valid datetime constant).

Best regards,

Hugo Kornelis


Post a Comment

<< Home