Calculating Midnight in a Time Zone - SQL Server

Code¶

Database View¶

create view dbo.LocationClock
as
select
     l.id
    ,CurrentTime = SYSDATETIMEOFFSET() at time zone tz.DotNetId
    ,MidnightTomorrowMorning = convert(datetime, convert(datetimeoffset,
            convert(varchar(10), dateadd(day,1,convert(date, SYSDATETIMEOFFSET() at time zone tz.DotNetId))) 
            + ' 00:00:00 ' 
            + 
            substring(convert(varchar(100), SYSDATETIMEOFFSET() at time zone tz.DotNetId), len(convert(varchar(100), SYSDATETIMEOFFSET() at time zone tz.DotNetId))-5, 6)
            ) at time zone 'UTC')
from
    dbo.[Location] l

    left join dbo.TimeZone tz
        on l.TimeZoneId = tz.Id
        

C# Class¶

      
public class LocationClock
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    public DateTimeOffset? CurrentTime { get; set; }

    [ForeignKey("Id")]
    public virtual Location Location { get;set; }

    public DateTime? MidnightTomorrowMorning { get; set; }
}