首页 > 代码库 > sql server 日期函数 [LocalTimeToUTC]、[UtcToLocalTime]、ConverToMidnight函数
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 GOtest
SELECT [dbo].[UtcToLocalTime](GETDATE())AS [ ] SELECT [dbo].[UtcToLocalTime](GETUTCDATE())AS [ ]
result
....
sql server 日期函数 [LocalTimeToUTC]、[UtcToLocalTime]、ConverToMidnight函数
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。