tim_harkin (9/26/2012)
Not the most efficient solution....
DECLARE @currentTime DATETIME
SET @currentTime = GETDATE()
SELECT CAST(DATEPART(year,@currentTime) AS CHAR(4))
+ CASE WHEN (DATEPART(month,@currentTime)) > 10 then (CAST(DATEPART(month,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(month,@currentTime) AS CHAR(1)) END
+ CASE WHEN (DATEPART(day,@currentTime)) > 10 THEN (CAST(DATEPART(day,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(day,@currentTime) AS CHAR(1)) END
+ CASE WHEN (DATEPART(hour,@currentTime)) > 10 THEN (CAST(DATEPART(hour,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(hour,@currentTime) AS CHAR(1)) END
+ CASE WHEN (DATEPART(minute,@currentTime)) > 10 THEN (CAST(DATEPART(minute,@currentTime) AS CHAR(2)))
ELSE '0' + CAST(DATEPART(minute,@currentTime) AS CHAR(1)) END
Tim, here's a trick for you;
SELECT CAST(DATEPART(year,@currentTime) AS CHAR(4))
+ RIGHT('0'+CAST(DATEPART(month,@currentTime) AS VARCHAR(2)),2)
+ RIGHT('0'+CAST(DATEPART(day,@currentTime) AS VARCHAR(2)),2)
+ RIGHT('0'+CAST(DATEPART(hour,@currentTime) AS VARCHAR(2)),2)
+ RIGHT('0'+CAST(DATEPART(minute,@currentTime) AS VARCHAR(2)),2)
Here's a quick way to meet OP's requirement;
SELECT CAST(REPLACE(REPLACE(REPLACE(CONVERT(CHAR(16),@currentTime,120),'-',''),' ',''),':','') AS CHAR(12))
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden