DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003', @Prod VARCHAR(MAX), @Product_Name VARCHAR(10) = 'Vehicles'

SET @Prod=@Product_name + RTRIM(@Product_name)
+ ' (Expires '+RTRIM(DATENAME(MM, @Expiry_Date ) + RIGHT(CONVERT(VARCHAR(12), @Expiry_Date ,107),9))+ ')'

SELECT @Prod

DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003';

SELECT DATENAME(MONTH, @Expiry_Date) + ' ' + DATENAME(DAY, @Expiry_Date) + ', ' + DATENAME(YEAR, @Expiry_Date);

DECLARE @Expiry_Date DATETIME = '2012-12-25 00:00:01.003', @Prod VARCHAR(MAX), @Product_Name VARCHAR(10) = 'Vehicles'

SET @Prod= 'Product: ' + @Product_name + ' (Expires ' + STUFF(CONVERT(VARCHAR(12), @Expiry_Date, 107), 1, 3, DATENAME(MM, @Expiry_Date)) + ')'

SELECT @Prod

DECLARE @product_name VARCHAR(20) = 'Vehicles' 
    ,@expiry_date DATETIME = '2012-09-10'

SELECT STUFF(RIGHT(CONVERT(VARCHAR(12), @expiry_date,107), 9), 1, 0
    ,'Product : ' + @product_name + ' (Expires ' + DATENAME(MM, @expiry_date)) + ')'