I didn't invent the code use in this function so can't take credit (or blame), but it comes in handy sometimes. I did turn it into an itvf and added a few extra formatting options so it can be used in a CROSS APPLY to quickly format any column of dates into any format you need. See additional example usage with a CROSS APPLY at bottom of post.
CREATE FUNCTION [dbo].[itvfFormatDateWithMask]
(
@date AS DATETIME
,@format_string VARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
/*
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY hh:mmtt')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMM Dx, YYYY')
'YYYY' - full year with century
'YY' - year without century
'Y' - last digit of year
'MMMM' - month name
'MMM' - month name first 3 chars only
'MM' - month number with leading zero
'M' - month number without leading zero)
'DDDD' - day name
'DD' - day number with leading zero
'D' - day number without leading zero
'HH' - hour with leading zero (24 hr format)
'H' - hour without leading zero
'hh' - hour with leading zero (12 hr format)
'h' - hour without leading zero (12 hr format)
'mm' - minutes with leading zero
'm' - minutes without leading zero
'ss' - seconds with leading zero
's' - seconds without leading zero
'fff' - milliseconds with leading zeros
'f' - milliseconds without leading zeros
'tt' - AM or PM
't' - A or P
'x' - st, nd, or th
*/
WITH fourRows(N)
AS (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
)
,cteTally(N)
AS (
SELECT TOP (50)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM
fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
ORDER BY 1
)
,tokenizedString
AS (
SELECT
N
,REPLACE(C,SUBSTRING(CAST(C AS NVARCHAR(50)),CHARINDEX('""',CAST(C AS NVARCHAR(50)),1),CHARINDEX('""',CAST(C AS NVARCHAR(50)),2)),'ZZZZZZZ') AS C
,groupId = DENSE_RANK() OVER (ORDER BY C, _groupId)
FROM
(
SELECT
N
,SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) AS C
,_groupId = N - ROW_NUMBER() OVER (PARTITION BY SUBSTRING(@format_string COLLATE Latin1_General_CS_AS,N,1) ORDER BY N)
FROM
cteTally
WHERE
N <= LEN(@format_string)
) AS fs)
SELECT
(SELECT
CASE REPLICATE(MIN(C),COUNT(*))
WHEN 'YYYY' THEN RIGHT('0000' + CAST(YEAR(@date) AS NVARCHAR(4)),4)
WHEN 'YY' THEN RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),2)
WHEN 'Y' THEN CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS NVARCHAR(4)),1) AS INT) AS NVARCHAR(2))
WHEN 'MMMM' THEN DATENAME(month,@date)
WHEN 'MMM' THEN LEFT(DATENAME(month,@date),3)
WHEN 'MM' THEN RIGHT('00' + CAST(MONTH(@date) AS NVARCHAR(2)),2)
WHEN 'M' THEN CAST(MONTH(@date) AS NVARCHAR(2))
WHEN 'DDDD' THEN DATENAME(weekday,@date)
WHEN 'DD' THEN RIGHT('00' + CAST(DAY(@date) AS NVARCHAR(2)),2)
WHEN 'D' THEN CAST(DAY(@date) AS NVARCHAR(2))
WHEN 'HH' THEN RIGHT('00' + CAST(DATEPART(hour,@date) AS NVARCHAR(2)),2)
WHEN 'H' THEN CAST(DATEPART(hour,@date) AS NVARCHAR(2))
WHEN 'hh' THEN RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
WHEN 'h' THEN
CASE WHEN LEFT(
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2),1) = 0 THEN
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),1)
ELSE
RIGHT('00' + CAST(
CASE DATEPART(hour,@date)
WHEN 12 THEN 12
ELSE DATEPART(hour,@date) % 12
END AS NVARCHAR(2)),2)
END
WHEN 'mm' THEN RIGHT('00' + CAST(DATEPART(minute,@date) AS NVARCHAR(2)),2)
WHEN 'm' THEN CAST(DATEPART(minute,@date) AS NVARCHAR(2))
WHEN 'ss' THEN RIGHT('00' + CAST(DATEPART(second,@date) AS NVARCHAR(2)),2)
WHEN 's' THEN CAST(DATEPART(second,@date) AS NVARCHAR(2))
WHEN 'fff' THEN RIGHT('000' + CAST(DATEPART(millisecond,@date) AS NVARCHAR(3)),3)
WHEN 'f' THEN CAST(DATEPART(millisecond,@date) AS NVARCHAR(3))
WHEN 'tt' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'PM'
ELSE N'AM'
END
WHEN 't' THEN
CASE
WHEN DATEPART(hour,@date) >= 12 THEN N'P'
ELSE N'A'
END
WHEN 'x' THEN
CASE
WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'
WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'
ELSE N'th'
END
ELSE
MIN(C)
END
FROM
tokenizedString
GROUP BY
groupId
ORDER BY
MIN(N)
FOR
XML PATH('')
,TYPE
).value('(./text())[1]','nvarchar(50)')
AS FormattedDate
)
/*
TESTING SCRIPTS
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[RandomDate] DATETIME NULL,
PRIMARY KEY (ID))
INSERT INTO #TempTable
SELECT RandomDate
FROM (
SELECT TOP 100
RandomDate = DATEADD(second,ABS(CHECKSUM(NEWID()))%36000, DATEADD(day,ABS(CHECKSUM(NEWID()))%3653+36524,'2000-01-01 00:00:01'))
FROM Master.dbo.SysColumns t1
)d
SELECT *
FROM #TempTable AS tt
CROSS APPLY
dbo.itvfFormatDateWithMask(tt.RandomDate,'MMMM Dx, YYYY h:mtt') AS d1
CROSS APPLY
dbo.itvfFormatDateWithMask(tt.RandomDate,'MM-DD-YYYY HH:mm') AS d2
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'YYYY-MM-DD HH:mm:ss.fff')
SELECT * FROM dbo.itvfFormatDateWithMask(GETDATE(),'MMMM Dx, YYYY h:mtt')
*/