Wiki

VERSAGO

All

How to left pad a string in SQL

<p>There may be cases in as SQL Select statement where you need to use the month (or day) from a date, but you need to have leading zeros (padding) to make the string consistent.&nbsp; Here are two examples of how the statement is constructed.&nbsp; These will return the month as 01, 02 .. 11, 12.&nbsp;&nbsp;This will work the same way for days.</p><p>SELECT RIGHT('0' + convert(varchar(2), month(GetDate())), 2)<br /><br />select right('0' + convert(varchar(2),Month(DocDate)),2)<br />from ORDR</p><p>To get both Year and Month (YYYYMM) you can use the following</p><p>SELECT<br />right('0' + convert(varchar(4),Year(DocDate)),4) + right('0' + convert(varchar(2),Month(DocDate)),2) as [YYYYMM]<br />from ORDR</p><p>To increase the padding, add additional padding characters and increase the size of the evaluation number.&nbsp; To get 001 use:</p><p>SELECT RIGHT('00' + convert(varchar(3), month(GetDate())), 3)</p>

HowTo

23 Jan 2018