Excellent functions. I've already tested them on my own server (2008) and added them to my toolbox of functions. In case you are interested, I added a tiny modification to both functions to handle Day Name Suffixes such as "1st," "2nd," "3rd," and "4th." This code piece can just be added right at the end of the "formattedDate" SELECT statement.
WHEN 'x' THEN
CASE
WHEN CAST(DAY(@date) AS INT) IN (1,21,31) THEN N'st'
WHEN CAST(DAY(@date) AS INT) IN (2,22) THEN N'nd'
WHEN CAST(DAY(@date) AS INT) IN (3,23) THEN N'rd'
ELSE N'th'
END
Now a mask like this can be used 'DDDD, MMMM DDx, YYYY' to produce 'Friday, March 23rd, 2012'.
Also, the difference between 'hh' and 'h' wasn't clear and didn't seem to produce different results no matter what dates I used for input. Since the 'MM' vs 'M', 'DD' vs 'D', 'HH' vs 'H' all produced the month or day with or without a leading zero respectively, I would assume the same would be the case for 'hh' and 'h'. So I modified the code as:
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
Perhaps there is a more efficient way of doing this, but I basically just took the code for 'hh' formatting and turned it into a case statement.
Now, to be thorough, I also added to the masking function of the ParseString function to allow for filtering out Day Name suffixes:
Added to the 'allowedTokens'
UNION ALL
SELECT
'x'
,'DAYSUFFIX'
Added to the 'pivotedTokens'
,daysuffix_value = ''
and
,[DAYSUFFIX]
Added to the 'verifiedTokens' pivot list
,[DAYSUFFIX]
WITHOUT these changes this: 'October 26th 2011', 'MMMM DD YYYY' will cause an error
WITH these changes this: 'October 26th 2011', 'MMMM DDx YYYY' parses the date successfully