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 …
I would prefer
SET @dt = RIGHT(CONVERT(varchar(4), YEAR(getdate())),2)
+ RIGHT(‘0’ +CONVERT(varchar(2), MONTH(getdate())),2)
+ RIGHT(‘0’ +CONVERT(varchar(2), DAY(getdate())),2)
LikeLike
Wow, that example was so ugly.
Although I think it’s time for a converter from date to a custom date string as in this connect issue:
https://connect.microsoft.com/SQLServer/feedback/details/530045
LikeLike
1.) Dev doesn’t know(or read) BOL is the ultimate reference
2.) He/she was a xbase developer
LikeLike
Mother of gawd that is terrible. CONVERT is the way to go.
LikeLike
Excellent post. I was checking continuously this blog and I’m impressed! Very useful info specifically the last part I care for such information a lot. I was seeking this particular information for a very long time. Thank you and best of luck.
LikeLike