How To Write Custom Date Format In SQL

SQL Server comes with several predefined dates and date time formats. However, I often get requests to create custom date format in SQL.

In this exercise, you will learn how to create a custom date format in SQL with the requirements stated below. Also, you will need the SQL Server Management Studio to complete this exercise.

Requirements

Using the Sample Superstore dataset:

1. Select the fields Customer Name, Segment, Product ID, Product Name, Sales, and Custom Order Date
2. Filter where the Region is East or West
3. Format the Order Date as follows:

Order Date Day	Format
1 or 21 and 31	1st, 21st, and 31st
2 and 22	2nd and 22nd
3 and 23	3rd and 23rd
Others	nth

The Order Date results should be formatted as follows:

• Sunday 1st March 2020
• Monday 2nd September 2019
• Thursday 3rd January 2019
• Thursday 22nd August 2019
• Friday 23rd August 2019
• Friday 4th January 2019

Dataset

Download the Sample Superstore dataset to follow along.

Solution

SELECT
	[Customer Name]
	,Segment
	,[Product ID]
	,[Product Name]
	,Sales
	,CONCAT(DATENAME(WEEKDAY, [Order Date]),' ' 
	,CASE 
		WHEN DATEPART(DAY,[Order Date]) IN (1,21,31) 
			THEN CONVERT(VARCHAR,DATEPART(DAY,[Order Date]))+'st'
		WHEN DATEPART(DAY,[Order Date]) IN (2,22) 
			THEN CONVERT(VARCHAR,DATEPART(DAY,[Order Date]))+'nd'
		WHEN DATEPART(DAY,[Order Date]) IN (3,23) 
			THEN CONVERT(VARCHAR,DATEPART(DAY,[Order Date]))+'rd'
		ELSE CONVERT(VARCHAR,DATEPART(DAY,[Order Date])) +'th'
	END ,' ' 
	,DATENAME(MONTH, [Order Date]) ,' ' 
	,YEAR([Order Date])) AS [Custom Order Date]
FROM Orders
WHERE 
	Region IN ('East', 'West')

Alternatively,

SELECT 
	[Customer Name]
	,Segment
	,[Product ID]
	,[Product Name]
	,Sales
	,CONCAT(DATENAME(WEEKDAY,[Order Date]),' ' 
	,CASE 
		WHEN DATEPART(DAY,[Order Date]) in (1,21,31) 
			THEN CONVERT(VARCHAR,DATEPART(DAY,[Order Date]))+'st'
		WHEN DATEPART(DAY,[Order Date]) in (2,22) 
			THEN CONVERT(VARCHAR,DATEPART(DAY,[Order Date]))+'nd'
		WHEN DATEPART(DAY,[Order Date]) in (3,23) 
			THEN CONVERT(VARCHAR,DATEPART(DAY,[Order Date]))+'rd'
		ELSE CONVERT(VARCHAR,DATEPART(DAY,[Order Date])) +'th'
	END ,' ' 
	,DATENAME(MONTH, [Order Date]) ,' ' 
	,YEAR([Order Date])) AS [Custom Order Date]
FROM Orders
WHERE 
	Region IN ('East', 'West')

I encourage you to learn more about the date formats available in SQL Server.

Happy Analyzing!