death to sql server (part 4)

I’ve written about this before, but to rehash … the date functions inside SQL server suck. What’s really weird is that there’s an undocumented way to retrieve out certain datetime formats, and even that is inconsistent in its numbering scheme.

The way to pull them out is by running “SELECT CONVERT(VARCHAR, GETDATE(), @x);” where @x is a positive integer. If you can find the right integer, you can save time and pull out something directly like ’11-10-2006′ as your variable.

One of the problems you’ll run into though is that you can’t just do 1 through $integer. Only some of them return something, and the ones that don’t just throw an SQL error, so you get to hunt down which integers return something.

Well, digging for them manually once is something I don’t want to repeat, so I wrote a query statement to pull out some of them. This could be a handy reference inside your database somewhere.

DECLARE @x int;
SET @x = 1;
WHILE @x 0 AND (@x 20 AND @x <25) OR (@x > 99 AND @x < 115) OR @x IN(126,130,131)) BEGIN
SELECT @x, CONVERT(VARCHAR, GETDATE(), @x);
END
SELECT @x = (@x +1);

END

Here’s another online reference you can use.

About these ads

1 Comment

Filed under Databases

One response to “death to sql server (part 4)

  1. I feel your pain, this drove me nuts too:

    http://joseph.randomnetworks.com/archives/2006/06/22/sql-server-convert/

    This was especially annoying after getting used excellent time and date features in PostgreSQL.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s