Search

Friday, January 25, 2008

All Your Dates are Belong to Us - Custom Date Formatting in SQL Server

Most people that use SQL Server are familiar with formatting dates inside SQL Server. The common approach is:
convert(varchar, getdate(), 106) -- Displays a date in the format 25 Jan 2008
That is all fine and good if your requirements are satisfied with the formats available inside SQL Server. SQL Server does provide a decent number of formats. Here is the list right from the SQL Server help:
Without century (yy) With century (yyyy) Standard Input/Output**
-0 or 100 (*) Defaultmon dd yyyy hh:miAM (or PM)
1101USAmm/dd/yy
2102ANSIyy.mm.dd
3103British/Frenchdd/mm/yy
4104Germandd.mm.yy
5105Italiandd-mm-yy
6106-dd mon yy
7107-Mon dd, yy
8108-hh:mm:ss
-9 or 109 (*) Default + millisecondsmon dd yyyy hh:mi:ss:mmmAM (or PM)
10110USAmm-dd-yy
11111JAPANyy/mm/dd
12112ISOyymmdd
-13 or 113 (*) Europe default + millisecondsdd mon yyyy hh:mm:ss:mmm(24h)
14114-hh:mi:ss:mmm(24h)
-20 or 120 (*) ODBC canonicalyyyy-mm-dd hh:mi:ss(24h)
-21 or 121 (*) ODBC canonical (with milliseconds)yyyy-mm-dd hh:mi:ss.mmm(24h)
-126(***)ISO8601yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
-130*Kuwaitidd mon yyyy hh:mi:ss:mmmAM
-131*Kuwaitidd/mm/yy hh:mi:ss:mmmAM
Wouldn't you know it, my requirements were not :-) I tried working with the built in formats and had to roll my own after all anyway. So here is the function I came up with: Please note: The function below relies on using regular expressions inside SQL Server. SQL Server does not have built in regular expression support so the function below relies on the method described in my other blog post about Using Regular Expression in SQL Server.
/*
Type:		Function
Name:		dbo.fnFormatDate
Author:		Boyan Kostadinov
Created:	01.25.2008
Dependencies:	master.dbo.fn_pcre_replace
Parameters:	@inputDate(datetime)
		- The date to format

		@formatString(varchar)
		- the format string to use (Examples "dd mm yyyy", "mmm.dd.yy")
Description:

Formats a given date based on the format specified in @formatString
d 	- one digit day (when applicable)
dd	- two digit day
ddd	- short day name
dddd	- long day name
m	- one digit month (when applicable)
mm	- two digit month
mmm	- short month name
mmmm	- long month name
yy	- two digit year
yyyy	- four digit year
*/
create function dbo.fnFormatDate
(
	@inputDate datetime,
	@formatString varchar(25)
)
returns varchar(20) as
begin
	declare @returnValue varchar(25)

	-- Declare local vairables
	declare @formattedDate varchar(25),
		@day varchar(20), @month varchar(20), @year varchar(20),
		@dayFormat varchar(5), @monthFormat varchar(5), @yearFormat varchar(5)

	set @dayFormat = ''
	set @monthFormat = ''
	set @yearFormat = ''

	-- Convert the supplied date to day mon year (25 Jan 2008)
	set @formattedDate = convert(varchar, @inputDate, 106)

	-- If the format string contains a format for the day
	if charindex('d', @formatString) > 0
		-- Get the day format string
		set @dayFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(d{1,4}).*', '$1')

	-- If the format string contains a format for the month
	if charindex('m', @formatString) > 0
		-- Get the month format string
		set @monthFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(m{1,4}|M{1,4}).*', '$1')

	-- If the format string contains a format for the year
	if charindex('y', @formatString) > 0
		-- Get the year format string
		set @yearFormat = master.dbo.fn_pcre_replace(@formatString, '.*?(y{2,4}).*', '$1')

	-- Format the day value based on the format string for the day
	select	@day =
		case @dayFormat
		when 'dd' then master.dbo.fn_pcre_replace(@formattedDate, '^(\d+).*', '$1')
		when 'ddd' then substring(datename(dw, @formattedDate), 1, 3)
		when 'dddd' then datename(dw, @formattedDate)
		else convert(varchar, day(@formattedDate))
	end

	-- Format the month value based on the format string for the month
	select	@month =
		case @monthFormat
		when 'mm' then master.dbo.fn_pcre_replace(convert(varchar, @inputDate, 101), '^(\d+)/.*', '$1')
		when 'mmm' then master.dbo.fn_pcre_replace(@formattedDate, '\d+\s(\w+)\s\d+', '$1')
		when 'mmmm' then datename(m, @formattedDate)
		else convert(varchar, month(@formattedDate))
	end

	-- Format the year value based on the format string for the year
	select	@year =
		case @yearFormat
		when 'yy' then substring(convert(varchar, year(@formattedDate)), 3, 2)
		else convert(varchar, year(@formattedDate))
	end

	set @returnValue = @formatString

	-- If the day format was specified
	if @dayFormat <> ''
		-- Replace the day format string with the actual day value
		set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @dayFormat, @day)

	-- If the month format was specified
	if @monthFormat <> ''
		-- Replace the month format string with the actual month
		set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @monthFormat, @month)
	
	-- If the year format was specified
	if @yearFormat <> ''
		-- Replace the year format string with the actual year
		set @returnValue = master.dbo.fn_pcre_replace(@returnValue, @yearFormat, @year)

	-- Return the formated value
	return @returnValue
end
To test this function, I created a table that hold the following date format string:
formatString             
-------------------------
dd MMM yy
dd MMM yyyy
dd-MM-yy
dd-MM-yyyy
dd.MM.yy
dd.MM.yyyy
dd/MM/yy
dd/MM/yyyy
ddMMMyy
ddMMMyyyy
MM-dd-yy
MM-dd-yyyy
MM/dd/yy
MM/dd/yyyy
MMM dd yyyy
MMM dd, yy
MMM dd, yyyy
MMMdd,yyyy
MMMddyyyy
yy.MM.dd
yy/MM/dd
yyMMdd
yyyy-MM-dd
yyyy.MM.dd
yyyy/MM/dd
yyyyMMdd
MMMyyyy
I tested the function with the simple SQL query:
select df.formatString,
dbo.fnFormatDate(getdate(), df.formatString)
as formattedDate
from dateFormats as df
And here are the results:
formatString              formattedDate       
------------------------- --------------------
dd MMM yy 25 Jan 08
dd MMM yyyy 25 Jan 2008
dd-MM-yy 25-01-08
dd-MM-yyyy 25-01-2008
dd.MM.yy 25.01.08
dd.MM.yyyy 25.01.2008
dd/MM/yy 25/01/08
dd/MM/yyyy 25/01/2008
ddMMMyy 25Jan08
ddMMMyyyy 25Jan2008
MM-dd-yy 01-25-08
MM-dd-yyyy 01-25-2008
MM/dd/yy 01/25/08
MM/dd/yyyy 01/25/2008
MMM dd yyyy Jan 25 2008
MMM dd, yy Jan 25, 08
MMM dd, yyyy Jan 25, 2008
MMMdd,yyyy Jan25,2008
MMMddyyyy Jan252008
yy.MM.dd 08.01.25
yy/MM/dd 08/01/25
yyMMdd 080125
yyyy-MM-dd 2008-01-25
yyyy.MM.dd 2008.01.25
yyyy/MM/dd 2008/01/25
yyyyMMdd 20080125
MMMyyyy Jan2008
// //]]>