Inline table-valued function without a parameter

Question

I am still pretty new to Azure platform, and learning SQL language. I have a question about creating a user-defined inline Table-valued function (without need a parameter) on Azure platform. Since accessing to Azure platform on my SQL Server Management Studio, whenever I create a log file by using the system built-in function GetDate(), it shows the date and time in UTC time zone, but I want my current time zone (Easter Time Zone) which is 4 hours behind of UTC. So, I am wondering if anyone can please guide me how I can create a inline-table-valued function that does not need any parameter, and able to use it as GetDate() function by simply running as below:

            SELECT GetDate()

This is my attempt and I know this is not correct, but at least I tried.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Azure_EST_GetDate()] (
)

RETURNS TABLE 
AS
RETURN 
(

    SELECT DATEADD(hh,-4,GETDATE()) AS 'handate'
)
GO

Thanks in advance,

FYI an in-line table-valued function without parameters is the same as a view! Rather create a view instead. It's simpler and you'll have a better change the SQL query optimiser will make a faster as well. However, in this case, a scalar UDF is better though, as @DanGuzman well describes below! — Reversed Engineer, Jan 27 '21 at 11:20

Dan Guzman · Accepted Answer · 2018-11-22T11:56:32.733

Consider using DATETIMEOFFSET and AT TIMEZONE instead of DATEADD. This will properly convert times across time zones and apply the appropriate time change rules. You can convert the DATETIMEOFFSET result to another type (datetime2 in this example) if needed.

I don't think your intent is to include the parenthesis in the function name so I removed it in this example.

CREATE FUNCTION [dbo].[Azure_EST_GetDate] (
)

RETURNS TABLE 
AS
RETURN 
(
    SELECT CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2(3)) AS handate
);
GO

EDIT

A table-valued function can generally be used in queries wherever a table may be specified. For example:

SELECT handate FROM dbo.Azure_EST_GetDate();

Table-valued functions are appropriate when more than one column or row is returned. You could alternatively use a scalar function for your need since a scalar (single) value is returned. Below is the equivalent scalar function.

CREATE FUNCTION [dbo].[Azure_EST_GetDate_Scalar] ()
RETURNS datetime2(3)
AS
BEGIN
    RETURN CAST(SYSDATETIMEOFFSET() AT TIME ZONE 'Eastern Standard Time' AS datetime2(3));
END;
GO

This scalar function can be used instead of GETDATE() in Azure SQL Database to return a local time:

SELECT dbo.Azure_EST_GetDate_Scalar() AS handate;

Thanks for your detailed explanation, and it works as I expected. I appreciated it. — harrisonthu, Oct 29 '18 at 14:51
since this newly created (table-valued function) will replace GetDate() function, so I am just curious if I want to use it in my stored procedure, like returning the result as a scalar function, so can you guide me how I can change it/use it? Thanks ! — harrisonthu, Nov 22 '18 at 00:41
@harrisonthu, I added usage examples to my answer along with a scalar function example. — Dan Guzman, Nov 22 '18 at 11:57

Inline table-valued function without a parameter

1 Answers1

Linked