SQL date formatting

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: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), @yearFormatvarchar(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