SQL: Get just the date part of a DATETIME on SQL Server 2005

In Microsoft SQL Server 2008+ there is a DATE type, and getting just the date portion of a DATETIME is as easy as a cast or convert to DATE. But, at my job, we're still using SQL Server 2005. What then?

A common technique is to cast to a float, which gives you a number where the part to the left of the decimal point is the date and the part to the right of the decimal point is the time. Wrap that in a FLOOR function to round down to the nearest whole number and you have effectively truncated the time portion. You can then cast that back to a DATETIME.

I have, however, read that doing it that way can be inefficient as it won't use DATETIME indexes. Another approach that it supposed to be more efficient is to use DATEADD and DATEDIFF like so:

1	SELECT DATEADD([dd], 0, DATEDIFF([dd], 0, GETDATE()));

Again, there is no reason to do that unless you are on a Microsoft SQL Server version prior to 2008.

SQL: TRY_CAST alternative for old versions of SQL Server

In modern versions of Microsoft SQL Server there is a TRY_CAST function which returns NULL if the type cast fails, and returns the converted value if it succeeds. This is helpful if you're trying to format numbers from non-sanitized input and somebody put malformed data into a field.

At my job, I'm still dealing with SQL Server 2005 which does not provide this function. An alternative that I found online was to use XML to accomplish the same result:

1	CAST('' AS XML).value('sql:column("ItemHeight") cast as xs:decimal ?', 'decimal(28,10)')

SQL: Replace repeated spaces with a single space

In SQL dialects that provide a REPLACE function you can replace instances of repeated spaces with a single space (you may replace CHAR(7) with another character that you know won't appear in your dataset):

1	SELECT REPLACE(REPLACE(REPLACE('some string with many spaces', ' ', ' ' + CHAR(7)), CHAR(7) + ' ', ''), ' ' + CHAR(7), ' ');