• 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))

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    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