Can't zero pad a variable

Question

Can't zero pad a variable

Viewing 3 posts - 16 through 17 (of 17 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

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?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Avi1 SSCrazy Points: 2323 More actions · Answer 2

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?

Yes, ran against the sample data, and the result was correct

ScottPletcher SSC Guru Points: 100948 More actions · Answer 3

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.