sql server 日期函數 [LocalTimeToUTC]、[UtcToLocalTime]、ConverToMidnight函數，sqlserver函數

sql server 日期函數 [LocalTimeToUTC]、[UtcToLocalTime]、ConverToMidnight函數，sqlserver函數

AX 2012表定義中，datetime類型預設值為utc微秒置0,之前的procedure 中日期有的是hardcode,談判有的是getdate(),在馬拉，太國，turky,US,格林威冶時間，us turky與祖國有時差

對比

select dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate())SELECT getdate(),getdate(),getdate(),getdate() 

同一查詢日期一致，通過dateadd,datepart將微秒置0

所以格林威冶之夜可以系

select  dateadd(hour, -datepart(hour,getutcdate()),dateadd(MINUTE, -datepart(MINUTE,getutcdate()),dateadd(second, -datepart(second,getutcdate()),dateadd(millisecond, -datepart(millisecond,getutcdate()),getutcdate()))))

當然可以用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] 通過DATEDIFF(second,  GETdate() , GETUTCDATE()) 攻取時差

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 @ConvertedUTCTime = DATEADD(second, @Offset, @LocalTimeToChange)-- return UTC DateTimeRETURN @ConvertedUTCTimeENDGO

測試

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 between UTC and Local timeSET @UTCDate = GETUTCDATE()SET @LocalDate = GETDATE()SET @TimeDiff = DATEDIFF(second, @UTCDate, @LocalDate)-- convert UTC to local DateTimeDECLARE @ConvertedLocalTime datetimeSET @ConvertedLocalTime = DATEADD(second, @TimeDiff, @UtcDateTime)-- return local DateTimeRETURN @ConvertedLocalTimeENDGO

test

SELECT [dbo].[UtcToLocalTime](GETDATE())AS [ ]SELECT [dbo].[UtcToLocalTime](GETUTCDATE())AS [ ]

result....