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.
You could also use the following syntax.
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 from 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.
Here is the result in SSMS as shown below in a screenshot.