UTC vs local time

Internally we should always use UTC when dealing with a DateTime variable. This ensures consistency as PaaS infrastructure in Azure always runs on UTC.

In C#, always prefer DateTime.UtcNow() rather than DateTime.Now(). In T-SQL, use SYSUTCDATETIME() rather than GETDATE().

The DateTime should then be converted to local time on the front-end of the system.

For database reporting, the UTC DateTime can be converted to local time using the AT SYSTEM TIME command:

SELECT myDateTime AT SYSTEM TIME 'UTC' AT SYSTEM TIME 'GMT standard' FROM myTable

Unit test considerations

To improve unit testing and also decouple the application from the frameworks application provider, it is recommended to provide an interface for use throughout the application:

public interface IDateTimeProvider
{
    public DateTime UtcNow { get; }
}

The concrete implementation can be made in infrastructure:

public class DateTimeProvider : IDateTimeProvider
{
    public DateTime UtcNow => DateTime.UtcNow;
}