SQL Zone is brought to you in partnership with:

I was born in Bulgaria. My immediate family and I relocated to Syracuse, NY in 1995. I completed high school in Syracuse, and then continued my education at Alfred University. My major at Alfred University was Computer Science. I also obtained a minor in Management Information Systems (MIS) to bridge the gap between technology and the business world. One of my future goals is to extend that bridge by obtaining a Master's degree in Business Administration. Boyan is a DZone MVB and is not an employee of DZone and has posted 27 posts at DZone. You can read more from them at their website. View Full User Profile

Custom Date Formatting in SQL Server

02.04.2008
| 98762 views |
  • submit to reddit
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
Published at DZone with permission of Boyan Kostadinov, author and DZone MVB.

(Note: Opinions expressed in this article and its replies are the opinions of their respective authors and not those of DZone, Inc.)

Tags:

Comments

Brad Hooper replied on Wed, 2008/05/07 - 12:07pm

This worked perfectly with only 1 change needed (to point to my previously created Regular Expression change function).  Just copied/pasted and created the function and worked perfectly!! 

I tweaked it to allow the time to be added to the date if you pass a 'Y' flag as a 3rd imput varable but the only formatting that is done for this is: convert(varchar, @inputDate, 114)  

 So if you pass "Y" to the function it will take the time from the input variable using the above function (if no time passed just formats all "0"'s in the time format.

 

arry singh replied on Wed, 2009/03/18 - 9:00am

hello sir

i used this function but it gave me this error

 Cannot find the user-defined function or aggregate "dbo.fn_pcre_replace", or the name is ambiguous.

due to this  dependenciy

Dependencies:   master.dbo.fn_pcre_replace

do u plz Provide me the solution,

I an stuck in a Problem , and for solving that i need  this fuction

Plz Plz help me , Thanxx In Advanced...

 

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.