Convert Date format into DD/MMM/YYYY format in SQL Server

Stage I: Date Conversion Using "CONVERT"

Your journey to transform date format in SQL Server begins by using the CONVERT function, which is the workhorse for date transformations. Here's your to-go command:

SELECT CONVERT(VARCHAR, your_date_column, 106) AS FormattedDate;


Note: Style 106 is no random magic number. It's the code we use to get the date string as "DD mon YYYY".

Stage II: Swapping Spaces with Slashes

You might've noticed that the result of your conversion is 07 Apr 2023, which doesn't fit the requirement of DD/MMM/YYYY. Time to call in REPLACE() for a character switch:

SELECT REPLACE(CONVERT(VARCHAR, your_date_column, 106), ' ', '/') AS FormattedDate;


That's more like it. Now the date is in the desired DD/MMM/YYYY format.

Getting Date Fancy: Post SQL Server 2012 Era

If you're sailing the high seas of SQL Server 2012 or later, things get fancier with the FORMAT() function:

SELECT FORMAT(your_date_column, 'dd/MMM/yyyy', 'en-US') AS FormattedDate;


P.S: Remember, FORMAT() is an aristocrat, might be slow in handling large data.

Language and Culture Settings: The Unsuspected Villains

Our handy CONVERT or FORMAT could play tricks if the server's language settings have a life of their own. To ensure dates don't get personality disorders, align language settings with your expected cultural formats:

SET LANGUAGE British;
SELECT CONVERT(VARCHAR, GETDATE(), 106);


Just like ordering tea in England, now CONVERT caters date in "DD mon YYYY" form in English.

The Legacy Combat: SQL Server 2005 era

Getting tangled in SQL Server 2005, with no FORMAT function at your mercy? Time to get your hands dirty with CLR UDFs (common language runtime user-defined functions):

-- CLR UDF Example (not actual code):
SELECT dbo.FormatDate(your_date_column) AS FormattedDate;


Consider this a tip from your friendly neighborhood SQL Spiderman.

Selecting the Right Wardrobe: Date Pattern

Whether you don the FORMAT() suit or go classic with CONVERT(), always remember to choose the correct pattern fitting the occasion: your SQL Server version.

Pay Attention to the Time

What's the time Mr. Wolf? If your data includes time components too, no worries. SQL Server can format datetime as niftily as date:

SELECT FORMAT(your_datetime_column, 'dd/MMM/yyyy hh:mm:ss tt', 'en-US') AS FormattedDateTime;


And just like that, your date has its watch set too!

Deeper Knowledge Dive

For SQL enthusiasts wanting more, check the References section. It's a mini database of its own on date formatting.