I read this article quite recently on the performance issues to do with Functions.

I'm currently in the development phase of a new database on the Azure SQL Database platform. It won't go live for a new months yet. I'm using a variety of scalar-valued functions with one being to convert UTC to Local Date where it is specified in the Config table, e.g. AUS Eastern Standard Time

The function is called as the following:

    dbo.fnGetLocalDate(DateColumn) as DateColumn,

Does anyone have any suggestions on how to avoid functions in cases such as this? I find them useful for code reuse and I'm not sure how to avoid it here.

I'm also wondering whether with vNext fixing the performance issues with Functions in the Azure platform that I'm better off continuing to work with functions.

CREATE FUNCTION [dbo].[fnGetLocalDate]
    @DateToConvert datetimeoffset = NULL
RETURNS datetimeoffset

    DECLARE @TimeZone varchar(50)

        WHEN @DateToConvert is NULL then NULL
        CONVERT(datetimeoffset, @DateToConvert AT TIME ZONE (SELECT Value FROM LookUp.Config WHERE Property = 'TimeZone'))

  • You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now without having to wait. Feb 6, 2018 at 11:16
  • Thanks for the suggestion @MartinSmith - would you mind responding with an answer?
    – Philip
    Feb 6, 2018 at 11:17
  • 1
    Functions aren't automatically bad (well, multi-statement table-valued functions can be). I wouldn't assume that just because you're using a function that you automatically have a problem here. Are you seeing issues within the execution plans or on wait stats on the queries? If not, I wouldn't necessarily sweat this particular function. It looks largely benign. Add to it @MartinSmith approach and you should be fine. Feb 6, 2018 at 12:41
  • FWIW, I've been down this road and you might want to consider just returning all values as UTC and let the presentation layer apply the time zone conversions as needed. It can get hairy to maintain on the database side over time. Otherwise, +1 for Martin's answer. Feb 6, 2018 at 14:28

2 Answers 2


You can rewrite it as an inline TVF returning a single column and row and CROSS APPLY it to get the benefits of inlining now (parallelism, no overhead of switching execution contexts, holistic query costing and optimisation) without having to wait for the work done on inlining of Scalar UDFs to get released.

So your function definition would be

CREATE FUNCTION [dbo].[fnGetLocalDate] (@DateToConvert DATETIMEOFFSET = NULL) 
                                     (SELECT Value 
                                      FROM   LookUp.Config 
                                      WHERE  Property = 'TimeZone')) AS 

With example usage

SELECT o.name, L.LocalDate
FROM sys.objects o
CROSS APPLY [dbo].[fnGetLocalDate](o.modify_date) AS L

You don't really need the CASE as it will return NULL on NULL input anyway and may get better plans without it


C# offers the System.TimeZoneInfo class. This class exposes 2 helpful methods. The first useful method is named FindSystemTimeZoneById(). By passing the time zone id to the method you can get the TimeZoneInfo for the given Id. For example, if your Azure Website was hosted in West Europe and you wanted the time for Munich, Germany, then you could use the following code set shown below.

var cetZone = TimeZoneInfo.FindSystemTimeZoneById("Central Europe Standard Time"); 
var cetTime = TimeZoneInfo.ConvertTimeFromUtc(DateTime.UtcNow, cetZone);
MunichTimeLabel.Text = cetTime.ToString();

It seems Microsoft is recommending using a SQL Server function on this article.

These two are the two options I know you can use.

  • Thanks Alberto, and yes that's where I got the initial idea from to do a Function call for this.
    – Philip
    Feb 6, 2018 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.