Rounding to DATETIME components

Sometimes you need to round DateTimes in T-SQL to a certain DateTime component. For example, you need to truncate the minutes and seconds and only keep the date and the hour. To do so, you can do this:

SELECT DATEADD(hh, DATEDIFF(hh, '20000101', getdate()), '20000101')

This turns 20120101 13:59:59 into 20120101 13:00:00. Please note that the comparison date 20000101 is totally arbitrary. You can use any day before the date you’re truncating.

Transpose lines into comma separated strings

Sometimes you need to return from a stored procedure the results of a SELECT as a comma-separated string.

Example: The SELECT returns:

But you want the stored procedure to return:
Hello, World, It's, Me

Instead of looping with a cursor you can solve this pretty elegantly like the following:

The AND Field IS NOT NULL at the end of the WHERE-clause is important, because otherwise the result would always be null if one of the values was null (because something + NULL = NULL).

Paginate results from T-SQL query

In MySQL you can use LIMIT to page results. There’s nothing like that in T-SQL, but with a little effort you get the same result:

This returns 15 entries starting at entry 45 in table tCustomer. Please note that the page number is zero-based here, that’s why we use ROW_NUMBER() OVER (ORDER BY LastName, FirstName)) – 1.