Instantly share code, notes, and snippets.

Embed
What would you like to do?
formatDate_Recursive_iTVF.sql #blog
-- http://spaghettidba.com/2012/03/23/sql-server-and-custom-date-formats/
-- =============================================
-- Author: Gianluca Sartori - @spaghettidba
-- Create date: 2011-10-14
-- Description: Formats a date using the supplied
-- format string
-- =============================================
CREATE FUNCTION [dbo].[formatDateWithReplace](@date AS datetime, @format_string varchar(50))
RETURNS TABLE
AS
RETURN (
-- ================================================
-- create the allowedTokens list
-- ================================================
WITH allowedTokens (id, code, value) AS (
SELECT id,
code COLLATE Latin1_General_CS_AS,
value
FROM (
SELECT 1, 'YYYY', RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4)
UNION ALL SELECT 2, 'YY', RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2)
UNION ALL SELECT 3, 'Y', CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) AS int) AS varchar(2))
UNION ALL SELECT 4, 'MMMM', CHAR(1)
UNION ALL SELECT 5, 'MM', RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
UNION ALL SELECT 6, 'M', CAST(MONTH(@date) AS varchar(2))
UNION ALL SELECT 7, 'DDDD', CHAR(2)
UNION ALL SELECT 8, 'DD', RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
UNION ALL SELECT 9, 'D', CAST(DAY(@date) AS varchar(2))
UNION ALL SELECT 10, 'HH', RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
UNION ALL SELECT 11, 'H', CAST(DATEPART(hour,@date) AS varchar(2))
UNION ALL SELECT 12, 'hh', RIGHT('00' + CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2)),2)
UNION ALL SELECT 13, 'h', CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2))
UNION ALL SELECT 14, 'mm', RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
UNION ALL SELECT 15, 'm', CAST(DATEPART(minute,@date) AS varchar(2))
UNION ALL SELECT 16, 'ss', RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
UNION ALL SELECT 17, 's', CAST(DATEPART(second,@date) AS varchar(2))
UNION ALL SELECT 18, 'fff', RIGHT('000' + CAST(DATEPART(millisecond,@date) AS varchar(3)),3)
UNION ALL SELECT 19, 'f', CAST(DATEPART(millisecond,@date) AS varchar(3))
UNION ALL SELECT 20, 'tt', CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
UNION ALL SELECT 21, 't', CASE WHEN DATEPART(hour,@date) >= 12 THEN 'P' ELSE 'A' END
UNION ALL SELECT 22, CHAR(1),DATENAME(month, @date)
UNION ALL SELECT 23, CHAR(2),DATENAME(weekday, @date)
) AS susbst (id, code, value)
),
-- ================================================
-- create the list of values to replace in the input
-- ================================================
substitutions (id, code, value, maxval) AS (
SELECT ROW_NUMBER() OVER (ORDER BY id, set_id), code, value, COUNT(*) OVER ()
FROM (
SELECT 0 AS set_id, id, code, value
FROM allowedTokens
) AS src
),
-- ================================================
-- make the format string case-sensitive
-- ================================================
formatStrings (formatString) AS (
SELECT @format_string COLLATE Latin1_General_CS_AS
),
-- ================================================
-- build a recursive CTE to replace tokens one at a time
-- ================================================
recursiveReplace AS (
SELECT s.id,
REPLACE(
f.formatString,
s.code,
s.value) AS formattedDate,
s.maxval
FROM formatStrings AS f
INNER JOIN substitutions AS s
ON s.id = 1
UNION ALL
SELECT s.id,
REPLACE(
r.formattedDate,
s.code,
s.value) AS formattedDate,
s.maxval
FROM recursiveReplace AS r
INNER JOIN substitutions AS s
ON s.id = r.id + 1
)
-- ================================================
-- selects the formatted string from the last row
-- in the recursive CTE
-- ================================================
SELECT formattedDate
FROM recursiveReplace
WHERE id = maxval
)
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment