Date to text string in format DD/MM/YY with leading 0 for month

[Corpcatalog]

I have a formula field which produces a batch id from 3 fields.

[@field:Cert]+'-'+CONVERT(VARCHAR,(Day([@field:BatchDeliveryDate]))) + CONVERT(VARCHAR,(Month([@field:BatchDeliveryDate]))) + CONVERT(VARCHAR,( Year([@field:BatchDeliveryDate])),2)+'-'+CONVERT(VARCHAR,([@field:Bags]))

This is not quite right as Month is only 1 character when the month is less than 10 and year is YYYY - I need a 6 digit string for the date

also  Field:Bags is an integer field and doesn't work for this, how do I convert the integer field to text - I get an error message when attempting to convert integer to text string.

[Corpcatalog]

Further to my post here 

I need the date converted to a text string in the form DDMMYY is there a simple solution?

 

I have fixed the integer field problem 

[CoopperBackpack]

Hello @Corpcatalog,

this formula gets the 'DDMMYY' from the date:

RIGHT('00' + CONVERT(nvarchar(2), DATEPART(day, [@field:BatchDeliveryDate])), 2)
+
RIGHT('00' + CONVERT(nvarchar(2), DATEPART(month, [@field:BatchDeliveryDate])), 2)
+
RIGHT(CONVERT(nvarchar(4), DATEPART(year, [@field:BatchDeliveryDate])), 2)

This formula adds leading 0 to the day and to the month.

[Corpcatalog]

Thats awesome thanks @CoopperBackpack