Tutorials
Search…
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.
  1. 1.
    Table valued – returns a table
  2. 2.
    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.
In [ ]:
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
In [ ]:
USE demo
DECLARE @UnixTimeStamp BIGINT = 1599624698
SELECT dbo.udf_ConvertUnixTimeStamp(@UnixTimeStamp) AS TheDate
Copy link