内联表值函数，不带参数 [英] Inline table-valued function without a parameter

问题描述

对于Azure平台，我仍然很陌生，并且正在学习SQL语言.我有一个关于在Azure平台上创建用户定义的内联表值函数(不​​需要参数)的问题.自从访问SQL Server Management Studio上的Azure平台以来，每当我使用系统内置函数 GetDate()创建日志文件时，它都会以UTC时区显示日期和时间，但我希望我的当前时区(Easter时区)比UTC晚4小时.因此，我想知道是否有人可以指导我如何创建不需要任何参数的内联表值函数，并能够通过简单地运行为 GetDate()函数来使用它下方:

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

预先感谢

推荐答案

考虑使用DATETIMEOFFSET和AT TIMEZONE而不是DATEADD.这样可以正确地跨时区转换时间并应用适当的时间更改规则.如果需要，可以将DATETIMEOFFSET结果转换为另一种类型(在此示例中为datetime2).

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

编辑

表值函数通常可用于可能指定表的查询中.例如:

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

此标量函数可以代替Azure SQL数据库中的GETDATE()来返回本地时间:

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;

这篇关于内联表值函数，不带参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！