SQL Server date functions [LOCALTIMETOUTC], [utctolocaltime], convertomidnight functions

In AX 2012 table definition, the datetime type default value is UTC microseconds 0, before the procedure date has some hardcode, negotiated some getdate (), in Mara, Taihang, Turky,us, Greenway smelter time, US Turky and the Motherland have time difference

Contrast

Same query date consistent, 0 microseconds via Dateadd,datepart

So the night of Greenway can be tied

Select  DateAdd (Hour,-datepart (Hour,getutcdate ()), DateAdd (MINUTE,-datepart ()), Minute,getutcdate ( Second,-datepart (Second,getutcdate ()), DateAdd (Millisecond,-datepart (Millisecond,getutcdate ()), getUTCDate ())))

Of course you can use CAST (GETDATE () as date)

Select CAST (GETDATE () as date) select cast (CAST (GETDATE () as date) as DATETIME)

• Convertomidnight

IF object_id (' dbo. Convertomidnight ') is not nullbegin PRINT ' dropping function dbo. Convertomidnight ' DROP FUNCTION dbo. Convertomidnightif @ @ERROR = 0 PRINT ' Function dbo. Convertomidnight dropped ' endgocreate FUNCTION dbo. Convertomidnight (@TimeToChange as         datetime) RETURNS datetime BEGIN    DECLARE @Midnight datetimeset @Midnight = DATEADD (Hour,-datepart (hour, @TimeToChange), DateAdd (MINUTE,-datepart (MINUTE, @TimeToChange), DATEADD (second,- DatePart (second, @TimeToChange), DateAdd (millisecond,-datepart (millisecond, @TimeToChange), @TimeToChange)))) RETURN @MidnightENDGOSELECT Dbo.convertomidnight (GETDATE ()) as Midnight

• Dbo. [LOCALTIMETOUTC] by DateDiff (second, GETdate (), getUTCDate ()) to the time difference

IF object_id (' dbo.[ LOCALTIMETOUTC] is not nullbegin PRINT ' dropping function dbo. [LOCALTIMETOUTC] ' DROP FUNCTION dbo. [LOCALTIMETOUTC] IF @ @ERROR = 0 PRINT ' Function dropped dbo. [LOCALTIMETOUTC] ' endgocreate FUNCTION dbo. [LOCALTIMETOUTC] (@LocalTimeToChange         As datetime) RETURNS datetimebegindeclare @ConvertedUTCTime datetime, @Offset INT--figure out the time difference between UTC and Local Timeset @Offset = DATEDIFF (Second,  GETdate (), getUTCDate ())--Convert Local DateTime to Utcset @Conver Tedutctime = DATEADD (Second, @Offset, @LocalTimeToChange)--Return UTC Datetimereturn @ConvertedUTCTimeENDGO

Test

SELECT getdate () as [Getdate],getutcdate () as [Getutcdate]select cast (' 2012-12-12 ' as DATETIME) as localtime, dbo. [LOCALTIMETOUTC] (' 2012-12-12 ') As [Localtimetoutc]select getdate () as [getdate],dbo. [LOCALTIMETOUTC] (GETDATE ()) As [LOCALTIMETOUTC]

• [dbo]. [UTCToLocalTime]

IF object_id (' [dbo].[ UTCToLocalTime] is not nullbegin PRINT ' dropping function [dbo]. [UTCToLocalTime] ' DROP FUNCTION [dbo]. [UTCToLocalTime] IF @ @ERROR = 0 PRINT ' Function [dbo]. [UTCToLocalTime] dropped ' Endgo CREATE FUNCTION [dbo]. [Utctolocaltime]dbo. [LOCALTIMETOUTC] (@UtcDateTime datetime) RETURNS datetimeasbegindeclare @UTCDate datetime, @LocalDate datetime, @TimeDiff int--figure out the time difference BETW Een UTC and Local timeset @UTCDate = getUTCDate () Set @LocalDate = GETDATE () Set @TimeDiff = DATEDIFF (Second, @UTCDate, @Loc Aldate)--convert UTC to local datetimedeclare @ConvertedLocalTime datetimeset @ConvertedLocalTime = DATEADD (Second, @Tim Ediff, @UtcDateTime)--return local Datetimereturn @ConvertedLocalTimeENDGO

Test

SELECT [dbo]. [UTCToLocalTime] (GETDATE ()) As []select [dbo]. [UTCToLocalTime] (getUTCDate ()) As []

Result ....

SQL Server Date function [LOCALTIMETOUTC], [utctolocaltime], convertomidnight function