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"
