0

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
AskMe
  • 2,495
  • 8
  • 49
  • 102
  • It doesn't appear that `FORMAT` supports this out of the box. [Here](https://www.itsupportguides.com/knowledge-base/sql-server/sql-how-to-output-ordinal-day-from-date-1st-2nd-3rd-4th/) is one way you can handle this. – Tim Biegeleisen May 13 '19 at 04:50

2 Answers2

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)

enter image description here

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
AnkUser
  • 5,421
  • 2
  • 9
  • 25
  • I'm not sure there is a need for `and (day(today))Mod 100 <>11` . It should work if you just use `and day(today) <>11` etc.. – Alan Schofield May 13 '19 at 09:16
0

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

Hungry_Developer
  • 61
  • 8