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:
SET ANSI_NULLS ON
GOCREATE 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 https://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
https://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
A typical statement to construct a file name using the function would go something like this:
SET @fn = ‘\\Server\Path\Prefix-‘
+ dbo.udf_DT_FilenameFmt (getdate(), 1, 1)
+ ‘.Ext’
PRINT ‘File Name is ->’ + @fn + ‘<-‘
GO
Results:
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.