I often come across a date manipulation requirement to have a leading zero for the single digit day/month number. Well, it is pretty easy to come up with a solution, but the other day I stumbled across a little gem which I would like to call the “think outside the square” approach.
Previously I would write something like:
select case when day('2009-07-01') < 10 then '0' else '' end + cast(day('2009-07-01') as varchar)
Nowadays, I simply apply the “think outside the square” approach to this problem:
select right('0' + cast(day('2009-09-01') as varchar), 2) select right('0' + cast(day('2009-10-19') as varchar), 2)