How to format date to Month day yr

How to format date to month day yr.

we can achive this in many simple way, here i will show you how to achive using scalar valued Function 

--Create Function (Scalar valued function)

CREATE FUNCTION [dbo].[ufnFormatDateToMonthDateYr](@date VARCHAR(16))

RETURNS VARCHAR(16)

AS

BEGIN

      DECLARE @formatted_month VARCHAR(3)

       DECLARE @formatted_day_no VARCHAR(4)

       DECLARE @formatted_year VARCHAR(4)

       DECLARE @day_no INT

       DECLARE @day_no_suffix VARCHAR(2)

       SET @formatted_month = ''

       SET @formatted_day_no = ''

       SET @day_no = 0

       SET @day_no_suffix = 'th'

      

       IF (@date IS NULL) RETURN 'undefined'

      

       SET @formatted_year = RIGHT(YEAR(@date), 2)

       SET @formatted_month = LEFT(DATENAME(month, DATEADD(month, MONTH(@date), 0) - 1), 3)

       SET @day_no = CAST(CAST(DAY(@date) AS VARCHAR(2)) AS INT)

      

       IF (@day_no < 4 OR @day_no >= 21)

       BEGIN

              IF (RIGHT(CAST(@day_no AS VARCHAR(2)), 1) = 1) SET @day_no_suffix = 'st'

              IF (RIGHT(CAST(@day_no AS VARCHAR(2)), 1) = 2) SET @day_no_suffix = 'nd'

              IF (RIGHT(CAST(@day_no AS VARCHAR(2)), 1) = 3) SET @day_no_suffix = 'rd'                       

       END

      

       SET @formatted_day_no = CAST(@day_no AS VARCHAR(2)) + @day_no_suffix

      

       RETURN  @formatted_month + ' ' + @formatted_day_no + ' ''' + @formatted_year

      

END

 

 GO

 

 

 

 Now run function

 

 SELECT [dbo].[ufnFormatDateToMonthDateYr] ('20211001')

 

 and Results will be "Oct 1st '21"