One of the most powerful features of SQL Server is the ability to create User Defined Functions (UDFs). These functions allow you to extend the functionality of SQL Server by creating your own custom functions that can be used in queries.

Recently, one of our readers, Nanda, shared a very detailed script for converting any date time into a desired format. This script utilizes a UDF called [dbo].[ufsFormat]. We highly recommend saving this script in your code bookmark for future reference.

UDFs are a great way to simplify complex queries and perform calculations on data. They can be used to manipulate strings, perform mathematical operations, and even format dates and times. The [dbo].[ufsFormat] function, for example, allows you to format a date or time value in any desired format.

Here are a few examples of how the [dbo].[ufsFormat] function can be used:

SELECT [dbo].[ufsFormat] ('8/7/2008', 'mm/dd/yy') 'Format mm/dd/yy'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'hh:mm:ss') 'Format hh:mm:ss'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'mmm') 'Format mmm'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', 'Mmm dd yyyy hh:mm:ss:ms AM/PM') 
	'Format Mmm dd yyyy hh:mm:ss:ms AM/PM'
GO
SELECT [dbo].[ufsFormat] ('8/7/2008', '#') 'Format #'
GO

As you can see, the [dbo].[ufsFormat] function allows you to specify the desired format using different format codes. This gives you the flexibility to display the date or time value in a format that suits your needs.

However, it’s important to note that in the latest version of SQL Server, there is a better function called FORMAT which can handle date and time formatting more efficiently. The FORMAT function provides a wide range of format options and is recommended for use in newer versions of SQL Server.

We encourage you to try out the [dbo].[ufsFormat] function and explore its capabilities. It can be a valuable tool in your SQL Server arsenal. And don’t forget to check out our new blog article on the FORMAT function for even more advanced formatting options.

Happy coding!