This post describes how you can create a user-defined function in SQL Server.

We will start off with a simple example that does not take any arguments in. It only returns the current date. You don’t need to actually write this yourself because SQL Server already has a function to do this for you. We use this example because it illustrates the syntax of creating and using functions.

CREATE FUNCTION [dbo].[GetSystemDateTime]() 
RETURNS DATETIME 
AS 
BEGIN 
    DECLARE @var datetime 
    SELECT @var=CURRENT_TIMESTAMP 
    RETURN @var 
END
GO

When you use your brand new function, be sure to include the two parentheses otherwise you will get the following message from SSMS.

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "dbo.GetSystemDateTime" could not be bound.

Below is how you use the function properly.

select dbo.GetSystemDateTime()

You could also use the following syntax.

select [dbo].[GetSystemDateTime]()

Get Julian Date

Here is a more useful function. It does not take any inputs. It returns today’s date in a seven-digit Julian format. If today was January 3, 2017, then the Julian date would be 2017003. Notice that it is not 20173. We need the zeros in front of the number of days since the last day of the previous year, if zeros are required to ensure that the day is three characters long.

create function [dbo].[ReturnNowAsJulianDate] ()
returns varchar(7)
as
   begin
      declare @date date;
      declare @julian varchar(7);
      set @date = getdate();
      set @julian =  RIGHT(CAST(YEAR(@date) AS CHAR(4)),4) + RIGHT('000' + CAST(DATEPART(dy, @date) AS varchar(3)),3)
       -- in the code above, "dy" is "day of year"
      return (@julian)
   end
go

Here is how you can call this function, remembering to use the two parentheses even though we don’t pass anything into the function.

select [dbo].[ReturnNowAsJulianDate]()

Here is the result in SSMS as shown below in a screenshot.