Output dates in MMDDYYYY format efficiently

I have the below script which results YYYYMMDD. However, I need the result in MMDDYYYY format.

Can someone please assist? I want no dash/stroke/periods between the numbers.

SELECT CONVERT(VARCHAR(10), RowUpdateDateTime, 112)
from MyTable

Results: 20141113

I want it to look like 11132014.

I tried the FORMAT syntax but it was taking forever to run.

回答1:

Take a look at the CONVERT() documentation: none of formats match exactly what you're looking for. It looks like 110 is the closest. We can finish by adding a REPLACE() call:

SELECT REPLACE(CONVERT(VARCHAR(10), RowUpdateDateTime, 110),'-','') from MyTable

I also wonder why you're doing this at all. Much of the time, a conversion like this can be handled more effectively by your client code.

回答2:

This is simple in SQL Server 2012.

The FORMAT function accepts standard .NET formatting patterns so you can specify exactly the one you want directly instead of manipulating the result of an approximately correct one.

SELECT FORMAT(RowUpdateDateTime,'MMddyyyy','en-us')

However I just noticed your comment that you already tried this and encountered performance issues.

This is the fastest one I tried so far.

SELECT CONCAT(CASE
                WHEN MONTH(D) < 10
                  THEN '0'
              END, MONTH(D), CASE
                               WHEN DAY(D) < 10
                                 THEN '0'
                             END, DAY(D), YEAR(D))

Testing against 10,000,000 dates this took 12 seconds, vs 21 Seconds for the REPLACE(CONVERT(VARCHAR(10), RowUpdateDateTime, 110),'-','') and 180 seconds for the FORMAT version.

Test script (uncomment the one under test)

    WITH 
    E1(N) AS 
    (
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL 
        SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
    )                                       -- 1*10^1 or 10 rows
    , E2(N) AS (SELECT 1 FROM E1 a, E1 b)   -- 1*10^2 or 100 rows
    , E4(N) AS (SELECT 1 FROM E2 a, E2 b)   -- 1*10^4 or 10,000 rows
    , E8(N) AS (SELECT 1 FROM E4 a, E4 b)   -- 1*10^8 or 100,000,000 rows
    , Dates(D) AS
    (
    SELECT TOP (10000000) DATEADD(MILLISECOND, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),0) AS N FROM E8   
    )
    SELECT 
    --12 Seconds
    MAX(CONCAT(CASE WHEN MONTH(D) < 10 THEN '0' END, MONTH(D), CASE WHEN DAY(D) < 10 THEN '0' END, DAY(D), YEAR(D)))
    --21 Seconds
    --MAX(REPLACE(CONVERT(VARCHAR(10), D, 110),'-',''))
    --180 Seconds
    --MAX(FORMAT(D,'MMddyyyy', 'en-us')) --18
    FROM Dates