How to achieve Date format SSRS Example 12Th Jan 2019

Question

Using expression, I'm able to get the date for mat like this in SSRS. Below is the expression : =FORMAT(Cdate(today), "dd-MM-yyyy")

However, I would like to see the date as: 12Th Jan 2019 or 1St Jan 2019 or 2nd Feb 2019.

How can I achieve this?

Answer 1

You can convert date to format like

01 Mar 2019 06:21:58:010 By using below convertion

SELECT CONVERT(NVARCHAR(30),GETDATE(),113)

but you can not convert like below directly

12Th Jan 2019 or 1St Jan 2019 or 2nd Feb 2019.

but you can achieve this by using the function

CREATE FUNCTION [dbo].[Ufn_GetDateWithSelectedFormat]
(
    @Date DATETIME 
)
RETURNS NVARCHAR(50)
AS
BEGIN

    DECLARE @DateValue NVARCHAR(50) = (SELECT CONVERT(NVARCHAR(50),GETDATE(),113))
    DECLARE @OnesPlaceNum INT = (SELECT SUBSTRING(@DateValue,1,2))

    SET @DateValue = CASE WHEN (@OnesPlaceNum % 10 = 1 AND @OnesPlaceNum % 100 <> 11) THEN (SELECT SUBSTRING(@DateValue,1,2)) + 'St' + (SELECT SUBSTRING(@DateValue,3,LEN(@DateValue)))
                      WHEN (@OnesPlaceNum % 10 = 2 @OnesPlaceNum % 100 <> 22) THEN (SELECT SUBSTRING(@DateValue,1,2)) + 'Nd' + (SELECT SUBSTRING(@DateValue,3,LEN(@DateValue)))
                      WHEN (@OnesPlaceNum % 10 = 3 @OnesPlaceNum % 100 <> 33) THEN (SELECT SUBSTRING(@DateValue,1,2)) + 'Rd' + (SELECT SUBSTRING(@DateValue,3,LEN(@DateValue)))
                      ELSE (SELECT SUBSTRING(@DateValue,1,2)) + 'Nd' + (SELECT SUBSTRING(@DateValue,3,LEN(@DateValue))) END

RETURN @DateValue
END
GO

Then you can call this function like

SELECT [dbo].Ufn_GetDateWithSelectedFormat then you are getting the answer like 13Rd May 2019 11:40:50:343

Answer 2

Here is the Expression which Gives you desired Result.

=day(today) &
IIF((day(today))Mod 10=1 and  (day(today))Mod 100 <>11,
"st",
IIF((day(today))Mod 10=2 and  (day(today))Mod 100 <>12,
"nd",
IIF((day(today))Mod 10=3 and  (day(today))Mod 100 <>13,
"rd",
"th")
)
)
& " " & MonthName(Month(today)) & " "& Year(today)