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
....