SQL Server T-SQL User-Defined Function of the Week

Formatting a Datetime for use in a File Name

Volume 1 Number 36    July 22, 2003

Visit http://www.NovickSoftware.com for additional material
about SQL Server, Visual Basic, ASP, XML, and security.

Visit the T-SQL UDF of the Week at:
http://www.NovickSoftware.com/UdfOfWeek/UDFofWeek.htm

Check out the UDF Frequently Asked Questions at:
http://www.novicksoftware.com/UdfOfWeek/UDF_FAQ.htm

To ask a UDF related question or to contribute a UDF,
send e-mail to udf@NovickSoftware.com

Sign up for this newsletter at:
http://www.novicksoftware.com/UdfOfWeek/UDFofWeekSignup.htm

I've been working with scripting SQL traces over the last few
weeks.  To minimize the overhead of the trace, it can be sent
to a file instead of the SQL Profiler or a table.  I'm working
on an article for Database Journal on the topic.  

As part of the process, a unique file name has to be constructed.
I use the date and time as part of the name of the disk file
that holds the trace information.  Adding the date and time 
to a file name is really just a matter of formatting.

File names can't have colons so the typical format for time
HH:MM:SS is out.  The also can't, or shouldn't, have slashes.

This week's UDF uses dashes instead of either the slash or the 
colon and a T to separate the date to from the time.  That's 
patterned after the ISO format representing date/time fields.

Here's the CREATE FUNCTION script:

/------- Start copying below this line -------------------------\
SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE FUNCTION dbo.udf_DT_FileNameFmt (

    @InputDT datetime -- Date/Time to format
  , @IncludeTime bit = 1 -- 1 to include the time
  , @IncludeMS   bit = 0 -- 1 to include milliseconds
)   RETURNS varchar(23) -- @InputDT as YYYY-MM-DDTHH-MM-SS-MILS
    WITH SCHEMABINDING -- Or Comment about why not
/* 
* Formats a date/time so that it can be used in a file name. It 
* changes any colons and periods to dashes and includes only the
* parts the user requests.  Time and milliseconds are optional.
*
* Example:
select dbo.udf_DT_FileNameFmt(Getdate(), 1, 1)
* Test:
PRINT 'Test 1    ' + CASE WHEN '2003-07-02' =
         dbo.udf_DT_FileNameFmt ('2003-07-02 14:22:33.123', 0, 0)
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 2    ' + CASE WHEN '2003-07-02T14-22-33' =
         dbo.udf_DT_FileNameFmt ('2003-07-02 14:22:33.123', 1, 0)
                     THEN 'Worked' ELSE 'ERROR' END
PRINT 'Test 3    ' + CASE WHEN '2003-07-02T14-22-33-123' =
         dbo.udf_DT_FileNameFmt ('2003-07-02 14:22:33.123', 1, 1)
                     THEN 'Worked' ELSE 'ERROR' END
* History:
* When       Who Description
* ---------- --- -----------------------------------------
* 2003-07-03 ASN Initial Coding
*
* © Copyright 2003 Andrew Novick http://www.NovickSoftware.com
* You may use this function in any of your SQL Server databases
* including databases that you sell, so long as they contain 
* other unrelated database objects. You may not publish this 
* UDF either in print or electronically.
* Published as T-SQL UDF of the Week  Vol 1 #35 7/15/03
http://www.NovickSoftware.com/UDFofWeek/UDFofWeek.htm
****************************************************************/
AS BEGIN

DECLARE @Result varchar(23)

SELECT @Result = REPLACE(
                     REPLACE (CONVERT(varchar(23), @InputDT, 126)
                               , ':', '-')
                       , '.', '-'
                        )
-- Adjust when time or milliseconds are not requested
IF @IncludeTime=0
    SET @Result = LEFT(@Result, 10)
ELSE IF @IncludeMS = 0
    SET @Result = LEFT(@Result, 19)
-- ENDIF                     

Return @Result
END

GO

GRANT EXEC, REFERENCES on dbo.udf_DT_FileNameFmt to PUBLIC
GO
\-------Stop copying above this line ---------------------------/

A typical statement to construct a file name using the function
would go something like this:

DECLARE @fn nvarchar(256)
SET @fn = '\\Server\Path\Prefix-'
        + dbo.udf_DT_FilenameFmt (getdate(), 1, 1)
        + '.Ext'
PRINT 'File Name is ->' + @fn + '<-'
GO        
(Results)
File Name is ->\\Server\Path\Prefix-2003-07-14T21-50-25-140.Ext<-

The output format of the function insures that the file 
names that use it sort by the time that the trace, or other
file, was created.  That comes in handy every once in a while.

There are two options to the function, the first asks that the
time be included. The second asks that the time include 
milliseconds.  

That's all there is to it.  I'll let you know when the article is 
published.

+--------------------------------------------------------------+
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
+--------------------------------------------------------------+