Table of Contents
KB Item 322
Product
VERSAGO
Product Version (if applicable)
All
Issue
How to left pad a string in SQL
Symptom
Cause
Resolution Notes
<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. Here are two examples of how the statement is constructed. These will return the month as 01, 02 .. 11, 12. 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. To get 001 use:</p><p>SELECT RIGHT('00' + convert(varchar(3), month(GetDate())), 3)</p>
KB Type
HowTo
Item Date
23 Jan 2018
