Lesson 37. User Defined Functions

User defined functions are another one of those elements of SQL Server that I have little use for. UDFs can be used to perform the same repetitive task. A good example is converting UNIX time stamps to a SQL Server data type.

There are two kinds of user defined functions.

Table valued – returns a table

Scalar valued – returns a single value

To see your functions in SSMS object explorer, expand your database, then expand the Programmability folder, then expand Functions.

UNIX timestamps are an efficient way to store datetime information. However, SQL Server does not understand UNIX timestamps. None of the date functions will work on a UNIX timestamp so you have to create your own function for the conversion. This is a common issue, so I use it as an example below.

Examples

Create Function

When creating and using functions, you have to specify the schema even if it is in dbo.

USE demo

DROP FUNCTION IF EXISTS dbo.udf_ConvertUnixTimeStamp

GO

CREATE FUNCTION dbo.udf_ConvertUnixTimeStamp (@UnixTimeStamp BIGINT)
RETURNS DATETIME
AS
BEGIN
DECLARE @LocalTimeOffset BIGINT = DATEDIFF(SECOND,GETDATE(),GETUTCDATE())
DECLARE @AdjustedLocalDatetime BIGINT;
SET @AdjustedLocalDatetime = @UnixTimeStamp - @LocalTimeOffset
RETURN (SELECT DATEADD(SECOND, @AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS DATETIME)))
END;
GO

How To Use A Function

USE demo

DECLARE @UnixTimeStamp BIGINT = 1599624698

SELECT dbo.udf_ConvertUnixTimeStamp(@UnixTimeStamp) AS TheDate