Instantly share code, notes, and snippets.

Embed
What would you like to do?
formatDate_Islands_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].[formatDateWithIslands](@date AS datetime, @format_string varchar(50))
RETURNS TABLE
AS
RETURN (
WITH
-- ================================================
-- Build 4 rows
-- ================================================
fourRows (N) AS (
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
),
-- ================================================
-- build a small 64 rows tally table
-- ================================================
cteTally (N) AS (
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM fourRows AS A
CROSS JOIN fourRows AS B
CROSS JOIN fourRows AS C
),
-- ================================================
-- create the allowedTokens list
-- ================================================
allowedTokens (code, value) AS (
SELECT code COLLATE Latin1_General_CS_AS, value
FROM (
SELECT 'YYYY', RIGHT('0000' + CAST(YEAR(@date) AS varchar(4)),4)
UNION ALL SELECT 'YY', RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2)
UNION ALL SELECT 'Y', CAST(CAST(RIGHT('00' + CAST(YEAR(@date) AS varchar(4)),2) AS int) AS varchar(2))
UNION ALL SELECT 'MMMM', DATENAME(month, @date)
UNION ALL SELECT 'MM', RIGHT('00' + CAST(MONTH(@date) AS varchar(2)),2)
UNION ALL SELECT 'M', CAST(MONTH(@date) AS varchar(2))
UNION ALL SELECT 'DDDD', DATENAME(weekday, @date)
UNION ALL SELECT 'DD', RIGHT('00' + CAST(DAY(@date) AS varchar(2)),2)
UNION ALL SELECT 'D', CAST(DAY(@date) AS varchar(2))
UNION ALL SELECT 'HH', RIGHT('00' + CAST(DATEPART(hour,@date) AS varchar(2)),2)
UNION ALL SELECT 'H', CAST(DATEPART(hour,@date) AS varchar(2))
UNION ALL SELECT 'hh', RIGHT('00' + CAST(CASE DATEPART(hour, @date) WHEN 12 THEN 12 ELSE DATEPART(hour, @date) % 12 END AS varchar(2)),2)
UNION ALL SELECT 'h', CAST(DATEPART(hour, @date) - (12 * CEILING((DATEPART(hour, @date) - 12)*.1)) AS varchar(2))
UNION ALL SELECT 'mm', RIGHT('00' + CAST(DATEPART(minute,@date) AS varchar(2)),2)
UNION ALL SELECT 'm', CAST(DATEPART(minute,@date) AS varchar(2))
UNION ALL SELECT 'ss', RIGHT('00' + CAST(DATEPART(second,@date) AS varchar(2)),2)
UNION ALL SELECT 's', CAST(DATEPART(second,@date) AS varchar(2))
UNION ALL SELECT 'fff', RIGHT('000' + CAST(DATEPART(millisecond,@date) AS varchar(3)),3)
UNION ALL SELECT 'f', CAST(DATEPART(millisecond,@date) AS varchar(3))
UNION ALL SELECT 'tt', CASE WHEN DATEPART(hour,@date) >= 12 THEN 'PM' ELSE 'AM' END
UNION ALL SELECT 't', CASE WHEN DATEPART(hour,@date) >= 12 THEN 'P' ELSE 'A' END
) AS susbst (code, value)
),
-- ================================================
-- make the parameter case-sensitive
-- ================================================
formatStringCS (format_string) AS (
SELECT @format_string COLLATE Latin1_General_CS_AS
),
-- ================================================
-- join the format string to the tally table
-- and add some rankings to identify the "islands"
-- ================================================
tokenizedString AS (
SELECT *,
groupId = DENSE_RANK() OVER(ORDER BY C, _groupId)
FROM (
SELECT N,
SUBSTRING(format_string, N, 1) AS C,
_groupId =
N - ROW_NUMBER() OVER (
PARTITION BY SUBSTRING(format_string, N, 1)
ORDER BY N
)
FROM cteTally, formatStringCS
WHERE N <= LEN(@format_string)
) AS fs
),
-- ================================================
-- Aggregate groups
-- ================================================
groupedTokens AS (
SELECT groupId,
groupLen = COUNT(*),
groupMin = MIN(N),
C = MIN(C)
FROM tokenizedString
GROUP BY groupId
)
-- ================================================
-- Join to tokens and allowedTokens
-- Uses the count inside the group to join to the
-- susbstitutions
-- Uses the groupMin to sort the output
-- Concatenate the results using FOR XML PATH('')
-- Uses .value to eliminate XML entities escape
-- ================================================
SELECT formattedDate = (
SELECT (
SELECT ISNULL(SUB.value, C)
FROM groupedTokens AS FS
LEFT JOIN allowedTokens AS SUB
ON REPLICATE(FS.C,groupLen) = SUB.code
ORDER BY groupMin
FOR XML PATH(''), TYPE
).value('(./text())[1]','nvarchar(50)')
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment