sql server 日期函数 [LocalTimeToUTC]、[UtcToLocalTime]、ConverToMidnight函数

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 NULL
BEGIN 
	PRINT 'Dropping function dbo.ConverToMidnight'
	DROP FUNCTION dbo.ConverToMidnight
	IF @@ERROR = 0 PRINT 'Function dbo.ConverToMidnight dropped'
END
go

CREATE FUNCTION dbo.ConverToMidnight (
	@TimeToChange         AS DATETIME
)
RETURNS DATETIME 
BEGIN
    DECLARE @Midnight DATETIME
	SET @Midnight= dateadd(hour, -datepart(hour,@TimeToChange),dateadd(MINUTE, -datepart(MINUTE,@TimeToChange),dateadd(second, -datepart(second,@TimeToChange),dateadd(millisecond, -datepart(millisecond,@TimeToChange ),@TimeToChange ))))
	RETURN @Midnight
END
GO
SELECT dbo.convertomidnight(GETDATE()) AS midnight

• dbo.[LocalTimeToUTC] 通过DATEDIFF(second,  GETdate() , GETUTCDATE()) 攻取时差

IF object_id('dbo.[LocalTimeToUTC]') IS NOT NULL
BEGIN 
	PRINT 'Dropping function dbo.[LocalTimeToUTC]'
	DROP FUNCTION dbo.[LocalTimeToUTC]
	IF @@ERROR = 0 PRINT 'Function dropped dbo.[LocalTimeToUTC]'
END
go

CREATE FUNCTION dbo.[LocalTimeToUTC]
( 
	@LocalTimeToChange         AS DATETIME
)
RETURNS DATETIME

BEGIN
	DECLARE @ConvertedUTCTime DATETIME
			,@Offset INT 
	-- Figure out the time difference between UTC and Local time
	SET @Offset = DATEDIFF(second,  GETdate() , GETUTCDATE()) 
	-- convert local DateTime to UTC
	SET @ConvertedUTCTime = DATEADD(second, @Offset, @LocalTimeToChange)	
	-- return UTC DateTime
	RETURN @ConvertedUTCTime
END
GO

测试

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 NULL
BEGIN 
	PRINT 'Dropping function [dbo].[UtcToLocalTime]'
	DROP FUNCTION [dbo].[UtcToLocalTime]
	IF @@ERROR = 0 PRINT 'Function [dbo].[UtcToLocalTime] dropped '
END
go
 
CREATE FUNCTION [dbo].[UtcToLocalTime]dbo.[LocalTimeToUTC]
(
	@UtcDateTime datetime
)
RETURNS DateTime
AS
BEGIN

DECLARE @UTCDate datetime
		, @LocalDate datetime
		, @TimeDiff INT
		
-- Figure out the time difference between UTC and Local time
SET @UTCDate = GETUTCDATE()
SET @LocalDate = GETDATE()
SET @TimeDiff = DATEDIFF(second, @UTCDate, @LocalDate)

-- convert UTC to local DateTime
DECLARE @ConvertedLocalTime datetime
SET @ConvertedLocalTime = DATEADD(second, @TimeDiff, @UtcDateTime)

-- return local DateTime
RETURN @ConvertedLocalTime
END
GO

test

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

result

....