A Function to Format Dates and Times Like in VB/VBA/VBSCRIPT

This week's UDF, is a user submission, FormatDate. It comes from reader David Garske.

The function is similar to VBA's Format function when used on a date with a user defined format. It allows the caller to specify exactly what parts of the date that they want in their output.

Here's the CREATE FUNCTION script:

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE FUNCTION [dbo].[FormatDate] (
    @date datetime, 
    @format varchar(100) = ''
)
RETURNS varchar(256)
AS
BEGIN 
 
/* 
Valid @format arguments
 nothing -  returns the date as varchar or a blank string if null
 y or yy - returns the last two digits of the year
 yyyy - returns the four digits of the year
 m  - returns the month
 mm  - returns the month zero filled forcing two digits
 d  - returns the day
 dd  - returns the day zero filled forcing two digits
 h  - returns the hour
 hh  - returns the hour zero filled forcing two digits
 n  - returns the minute
 nn  - returns the minute zero filled forcing two digits
 s  - returns the second
 ss  - returns the second zero filled forcing two digits
 l - returns millisecond
 else - returns itself
*/
 
declare @fmtxt varchar(100)
declare @frmtemp varchar(4)
declare @fmchar varchar(1)
declare @i int
 
--Return blank if date is null
if (@date = '' or @date is null) return ''
 
--Return date as string if format is blank
if (@format is null or @format='') return convert(varchar, @date)
 
set @fmtxt = ''
set @I = 1
set @format = lower(@format) --in case the server is set to case sensitvity
 
--loop through each format character
while (@i < len(@format) + 1)
begin
 set @fmchar = substring(@format, @i, 1)
 if (@fmchar = 'y')
 begin
  --see if next character is the same
  if (substring(@format, @i+3, 1) = @fmchar)
  begin
   set @fmtxt = @fmtxt + convert(varchar, datepart(yyyy, @date))
   set @i = @i + 3
  end
  else
  begin
   set @fmtxt = @fmtxt + right(convert(varchar, datepart(yyyy, @date)), 2)
   if (substring(@format, @i+1, 1) = @fmchar) set @i = @i + 1
  end
  
 end
 else if (@fmchar = 'm')
 begin
  --see if next character is the same
  if (substring(@format, @i+1, 1) = @fmchar)
  begin
   set @frmtemp = convert(varchar, datepart(m, @date))
   if (len(@frmtemp) = 1) set @frmtemp = '0' + @frmtemp
   set @fmtxt = @fmtxt + @frmtemp
   set @i = @i + 1
  end
  else
   set @fmtxt = @fmtxt + convert(varchar, datepart(m, @date))
 end
 else if (@fmchar = 'd')
 begin
  --see if next character is the same
  if (substring(@format, @i+1, 1) = @fmchar)
  begin
   set @frmtemp = convert(varchar, datepart(d, @date))
   if (len(@frmtemp) = 1) set @frmtemp = '0' + @frmtemp
   set @fmtxt = @fmtxt + @frmtemp
   set @i = @i + 1
  end
  else
   set @fmtxt = @fmtxt + convert(varchar, datepart(d, @date))
 end
 else if (@fmchar = 'h')
 begin
  --see if next character is the same
  if (substring(@format, @i+1, 1) = @fmchar)
  begin
   set @frmtemp = convert(varchar, datepart(hh, @date))
   if (len(@frmtemp) = 1) set @frmtemp = '0' + @frmtemp
   set @fmtxt = @fmtxt + @frmtemp
   set @i = @i + 1
  end
  else
   set @fmtxt = @fmtxt + convert(varchar, datepart(hh, @date))
 end
 else if (@fmchar = 's')
 begin
  --see if next character is the same
  if (substring(@format, @i+1, 1) = @fmchar)
  begin
   set @frmtemp = convert(varchar, datepart(s, @date))
   if (len(@frmtemp) = 1) set @frmtemp = '0' + @frmtemp
   set @fmtxt = @fmtxt + @frmtemp
   set @i = @i + 1
  end
  else
   set @fmtxt = @fmtxt + convert(varchar, datepart(s, @date))
 end
 else if (@fmchar = 'n')
 begin
  --see if next character is the same
  if (substring(@format, @i+1, 1) = @fmchar)
  begin
   set @frmtemp = convert(varchar, datepart(n, @date))
   if (len(@frmtemp) = 1) set @frmtemp = '0' + @frmtemp
   set @fmtxt = @fmtxt + @frmtemp
   set @i = @i + 1
  end
  else
   set @fmtxt = @fmtxt + convert(varchar, datepart(n, @date))
 end
 else if(@fmchar = 'l')
  set @fmtxt = @fmtxt + convert(varchar, datepart(ms, @date))
 else
  set @fmtxt = @fmtxt + @fmchar
 
 set @i = @i + 1
end
 
return  @fmtxt
 
END
GO
 
GRANT EXEC on dbo.FormatDate to PUBLIC
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Let's test out the function on a few examples:

declare @TestDT datetime
SET @TestDT = '2004-06-15 08:55:23.123'

select dbo.FormatDate (@TestDT, 'yyyy-mm-dd hh:nn:ss.l')[@TestDT]
     , dbo.FormatDate (@TestDT, 'mm-hh-mm') [mm-hh-mm]
     , dbo.FormatDate (@TestDT, 'l.s:nn:hh dd-mm-yyyy') 
                           [l.s:nn:hh dd-mm-yyyy]

GO

(Results)
@TestDT                 mm-hh-mm  l.s:nn:hh dd-mm-yyyy   
----------------------- --------- -----------------------
2004-06-15 08:55:23.123 06-08-06  123.23:55:08 15-06-2004

Looks pretty good to me. There are a couple of things to note before using this function. The first thing to notice is the way to specify minutes: it's nn instead of mm. I'm sure that made it easier to write the UDF and made it faster as well.

You should also be aware that SQL isn't usually the best place to format dates. If there is a report writer or client application involved, they can usually format dates much more quickly and relieve SQL Server of that task.

As always with UDFs, performance is a consideration. Using a scalar UDF on a few or even a few hundred data rows isn't much of a problem. If you try and use any scalar UDF on thousands or millions of rows you're likely to create a performance problem for yourself.

Thanks to David for his contribution.

Do you have a great UDF that you'd like to share?  Or maybe you have a T-SQL problem that you think could be solved by a UDF but you don't know how? Send them to: UDF@NovickSoftware.com

and they might be published in this newsletter.  I try and respond to every request that I get. 

Thanks, 
Andrew Novick