Jasinski Technical Wiki

Navigation

Home Page
Index
All Pages

Quick Search
»
Advanced Search »

Contributor Links

Create a new Page
Administration
File Management
Login/Logout
Your Profile

Other Wiki Sections

Software

PoweredBy

Calculating Midnight in a Time Zone - SQL Server

RSS
Modified on Wed, Sep 01, 2021, 6:13 AM by Administrator Categorized as SQL Server, ┬ĚNet Framework

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; }
}

ScrewTurn Wiki version 3.0.1.400. Some of the icons created by FamFamFam. Except where noted, all contents Copyright © 1999-2022, Patrick Jasinski.