Home Forums SQL Server 2012 SQL Server 2012 - T-SQL Can't zero pad a variable RE: Can't zero pad a variable

  • October 13, 2017 at 2:37 pm

    #1963651

    Luis Cazares - Friday, October 13, 2017 1:53 PM

    Avi1 - Friday, October 13, 2017 12:40 PM

    I tested the scripts in one of dev box in SQL2016. the results are below. Each scripts ran 100 times (at 2 different times)
    Included the method suggested by Scott Pletcher 

    CONVERT(CHAR(2), SomeDate, 1)

    Run#1
    Method        AvgTime(ms)
    PRECAST Method        553.07    
    DATENAME Method        518.48    
    MATH OVERRUN Method    526.75    
    ScottPletcher Method    483.83    

    Ru#2
    PRECAST Method        565.5    
    DATENAME Method        515.25    
    MATH OVERRUN Method    526.97    
    ScottPletcher Method    501.11

    Sure, it can be faster. Are you sure that it's correct?

    Yeah, should be Day instead of Month, but that's extremely easy to fix: just use code 3 instead of 1:

    CONVERT(CHAR(2), SomeDate, 3)

    I'd avoid the other method here just because it's so obscure and uncommon.  It's very common to convert a date to char, but extraordinarily uncommon to add 100 to the days of a month(!).  It'd have to perform significantly better for me to even consider such a trick. [Sure Celko uses 00 as a day,but even he doesn't add 100 to it :laugh:.]

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.