Can anyone please help me with this, How can I convert getdate() to format 22nd APR, 2022, 21st APR, 2022, 23rd APR,2022, 24th APR, 2022, nd or th or rd is important here.

So far I have done this,

SELECT FORMAT (getdate(), 'dd MMM, yyyy') as date

Thanks in advance !

CodePudding user response：

For SQLServer:

SELECT FORMAT(getdate(),'d'
 IIF(DAY(getdate()) IN (1,21,31),'''st'''
,IIF(DAY(getdate()) IN (2,22),'''nd'''
,IIF(DAY(getdate()) IN (3,23),'''rd''','''th''')))
 ' MMM'  ', '  'yyyy') As [Formatted Date]

Here is a demo

CodePudding user response：

Maybe a query like below

SELECT FORMAT (getdate(), 'd''' v.suff ''' MMM, yyyy') as date
FROM
(values (1,'st'), (2,'nd'),(3,'rd'), (4,'th')
, (5,'th'), (6,'th'), (7,'th'), (8,'th')
, (9,'th'), (0,'th'))v(num,suff)
WHERE DAY(getdate())=v.num

Demo link

CodePudding user response：

This one cover the 11th,12th and 13th.

select 
cast(day(getdate()) as nvarchar)  
case when day(getdate()) in (11,12,13)
    then 'th'
else
case right(day(getdate()),1)
        when 1 then
            'st'
        when 2 then 
            'nd'
        when 3 then
         'rd'
        else 'th'
    end
end   format(getdate(),' MMM yyyy') as suffixed

CodePudding user response：

I would perform this formatting at the presentation tier - reporting tools, client languages like C# and PHP, etc. all have more elaborate and efficient date formatting methods than T-SQL.

If it has to be done in T-SQL, I would avoid FORMAT() because it's slow as molasses and since, presumably, you're going to call this more than once or for sets, I would put it in an inline table-valued function which will be more efficient than a scalar UDF and much more maintainable than putting this conditional logic inline. Using the CASE expression from the accepted answer over here:

CREATE FUNCTION dbo.SpecificFormat(@d date)
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    WITH s(d) AS 
    (
      SELECT REPLACE(UPPER(CONVERT(varchar(13), @d, 106)),' ','.')
    ),
    p(d,m,y) AS
    (
      SELECT PARSENAME(d,3), PARSENAME(d,2), PARSENAME(d,1)
      FROM s
    )
    SELECT OutputFormat = CONCAT_WS(' ', d   CASE 
      WHEN d % 100 IN (11,12,13) THEN 'th'
      WHEN d % 10 = 1 THEN 'st'
      WHEN d % 10 = 2 THEN 'nd'
      WHEN d % 10 = 3 THEN 'rd'
      ELSE 'th' END, m   ',', y)
    FROM p
  );

Examples:

DECLARE @d date = getdate();
SELECT OutputFormat FROM dbo.SpecificFormat(@d);

Output:

OutputFormat
22nd APR, 2022

SELECT o.name, f.OutputFormat
  FROM sys.objects AS o
  CROSS APPLY dbo.SpecificFormat(o.modify_date) AS f;

Output:

name	OutputFormat
SpecificFormat	22nd APR, 2022
QueryNotificationErrorsQueue	13th APR, 2009
queue_messages_1977058079	24th SEP, 2019
...	...

• Example db<>fiddle