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

Paul Rajs

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

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Suraj Kumar7y ago
You can try this customized query
SELECT
CASE DAY(GetDate())
    WHEN 1 THEN Convert(Varchar,DAY(GetDate())) + 'st'+ + ' '+ SUBSTRING( Convert(Varchar,DateName(month, (GetDate()))),1,3) + ' '+ Convert(Varchar,YEAR(GetDate()))
    WHEN 2 THEN Convert(Varchar,DAY(GetDate())) + 'nd'+ + ' '+ SUBSTRING( Convert(Varchar,DateName(month, (GetDate()))),1,3) + ' '+ Convert(Varchar,YEAR(GetDate()))
    WHEN 3 THEN Convert(Varchar,DAY(GetDate())) + 'rd'+ + ' '+ SUBSTRING( Convert(Varchar,DateName(month, (GetDate()))),1,3) + ' '+ Convert(Varchar,YEAR(GetDate()))
    ELSE Convert(Varchar,DAY(GetDate())) + 'th'+ + ' '+ SUBSTRING( Convert(Varchar,DateName(month, (GetDate()))),1,3) + ' '+ Convert(Varchar,YEAR(GetDate()))
END AS DateValue
Replace GetDate() with your table column name or you can create a function also using above query and pass the date to that function. For today it is giving the output as shown below.
DateValue
1st Nov 2018
Hope this will help you.
0
Sreekanth Reddy7y ago
@Paul Rajs

Please check with the following query too..

SELECT 'Reservation ' + DATENAME(m, @BeginningStay) + ' ' + DATENAME(d, @BeginningStay) +
CASE
WHEN DATENAME(d, @BeginningStay) IN ('1', '21', '31') THEN 'st'
WHEN DATENAME(d, @BeginningStay) IN ('2', '22') THEN 'nd'
WHEN DATENAME(d, @BeginningStay) IN ('3', '23') THEN 'rd'
ELSE 'th'
END

From the below URL :
https://www.sqlteam.com/forums/topic.asp?TOPIC_ID=34549
0
Sreekanth Reddy7y ago
@Paul Rajs

Please have a look in the below URL once :

https://raresql.com/2013/08/17/sql-server-how-to-add-st-nd-rd-th-to-dates/

--This script is compatible with SQL Server 2012 and above.
USE [tempdb]
GO
SELECT [ID],[Date],FORMAT([Date],'d'
+IIF(DAY([Date]) IN (1,21,31),'''st'''
,IIF(DAY([Date]) IN (2,22),'''nd'''
,IIF(DAY([Date]) IN (3,23),'''rd''','''th''')))
+' MMMM yyyy') As [Formatted Date]
FROM tbl_sample

0