How to Concatenate Date and Get date "2nd Nov 18" format sql

Question

Hi developers ,

 

In my project i need to get the date as below format like

Actual date : 2018-11-02 

by the below sql query i got output like the following "2 Nov 18".thers is nothing issue . 

 

"

SELECT pldg.PledgeId, pldg.PaidThruDate, pldg.Status, prd.PledgeType, prd.ProductCode, prd.ProductID, prd.ProductName, prd.Shortdescription,

pldg.LeadID, REPLACE(RIGHT(CONVERT(VARCHAR(12), pldg.LastPayment_Date, 106), 12), ' ', '') AS LastPayment_Date FROM Pledge_PledgeDetails pldg

INNER JOIN pledge_ProductDetails prd ON pldg.ProductID=prd.ProductID WHERE pldg.LeadID=344435 AND pldg.PledgeType=2 AND pldg.Status in (1,2)

ORDER BY pldg.Fulfilled_Date

" 

but my Expected output is if

1.if date is 1 Nov 18 Output will be like 1st Nov 18 

2.if date is 2 Nov 18 Output will be like 2nd Nov 18

3.if date is 3 Nov 18 Output will be like 3rd Nov 18

4.if date is 4 Nov 18 Output will be like 4th Nov 18

5.if date is 5 Nov 18 Output will be like 5th Nov 18 

  

1.if date is 11 Nov 18 Output will be like 11th Nov 18

2.if date is 12 Nov 18 Output will be like 12th Nov 18

3.if date is 13 Nov 18 Output will be like 13th Nov 18

4.if date is 14 Nov 18 Output will be like 14th Nov 18

5.if date is 15 Nov 18 Output will be like 15th Nov 18

 

1.if date is 21 Nov 18 Output will be like 21st Nov 18

2.if date is 22 Nov 18 Output will be like 22nd Nov 18

3.if date is 23 Nov 18 Output will be like 23rd Nov 18

4.if date is 24 Nov 18 Output will be like 24th Nov 18

5.if date is 25 Nov 18 Output will be like 25th Nov 18

 

i am trying with google , but i didn't get exact output . if anyone know how to perform this task , kindly suggest me to i done this task .

 

thanking you

Paul.S