Refactor This: Runaway T-SQL to Print YYMMDD

Recently trying to debug some really cryptic T-SQL scripts.

Guess what this is trying to do?

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

DECLARE @dt varchar(6)
SET @dt = RIGHT(CONVERT(varchar(4), YEAR(getdate())),2) + REPLICATE(‘0’, 2 – LEN(CONVERT(varchar(2), MONTH(getdate())))) + CONVERT(varchar(2), MONTH(getdate())) + REPLICATE(‘0’, 2 – LEN(CONVERT(varchar(2), DAY(getdate())))) + CONVERT(varchar(2), DAY(getdate()))

[/sql]

Answer:

It’s trying to print the current date in YYMMDD format.

Uhm, there’s a much better way to do this.

Try CONVERT.

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

SET @dt = CONVERT(VARCHAR(6), GETDATE(), 12)


[/sql]

I still quite enjoy debugging or refactoring T-SQL scripts, but sometimes I’m just amazed at what I still see …

Advertisements

5 comments

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