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), ' ');

Rosetta Code

Rosetta Code is a programming chrestomathy site. The idea is to present solutions to the same task in as many different languages as possible, to demonstrate how languages are similar and different, and to aid a person with a grounding in one approach to a problem in learning another.

For example, to calculate the Fibonacci sequence in ArnoldC:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

IT'S SHOWTIME HEY CHRISTMAS TREE f1 YOU SET US UP @I LIED TALK TO THE HAND f1 HEY CHRISTMAS TREE f2 YOU SET US UP @NO PROBLEMO HEY CHRISTMAS TREE f3 YOU SET US UP @I LIED STICK AROUND @NO PROBLEMO GET TO THE CHOPPER f3 HERE IS MY INVITATION f1 GET UP f2 ENOUGH TALK TALK TO THE HAND f3 GET TO THE CHOPPER f1 HERE IS MY INVITATION f2 ENOUGH TALK GET TO THE CHOPPER f2 HERE IS MY INVITATION f3 ENOUGH TALK CHILL YOU HAVE BEEN TERMINATED

Bit-Twiddling Hacks

Sean Eron Anderson has collected a great bunch of bit-twiddling hacks at his website. Even if you may never have a need for operating at this level, it's a good mental exercise to to solve these kinds of puzzles.

For example, a C macro to swap two values without use of a third variable and by only using addition and subtraction:

1 2	#define SWAP(a, b) ((&(a) == &(b)) || \ (((a) -= (b)), ((b) += (a)), ((a) = (b) - (a))))

If you really want to jump head-first into the deep end, check out the book Hacker's Delight by Henry S. Warren.