如何日期差毫秒 [英] How to date difference millsecond
本文介绍了如何日期差毫秒的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要日期差价第二次
选择DATEADD(Day,DATEDIFF(Day,0,getdate()) - 1,-0.0000001)
2018-07-23 23:59:59.993
但我需要2018-07-23 23:59 :59.999
我的尝试:
我试过添加零
选择DATEADD(Day,DATEDIFF(Day,0,getdate()) - 1,-0.00000001)
2018-07-24 00:00:00.000
但我除外
2018-07-23 23:59:59.999
解决方案
假设SQL 2008或更高版本,您需要使用 new datetime2(7)type [ ^ ]。
SELECT DateAdd(毫秒,-1,强制转换(DateAdd(day,-1,Cast(SysDateTime() As date )) as datetime2( 7 )));
- 输出:2018-07-23 23:59:59.9990000
打破这种局面:
DECLARE @ Now datetime2( 7 )= SysDateTime(); - 获取当前日期和时间
DECLARE @ Today date = Cast( @现在 作为 日期); - 删除时间部分
DECLARE @昨天 date = DateAdd(day,-1, @Today 跨度>); - 减去一天
DECLARE @ YesterdayDateTime datetime2( 7 )= Cast( @昨天 作为 datetime2( 7 )); - 需要datetime2来减去时间单位
DECLARE @ Result datetime2( 7 )= DateAdd(毫秒,-1, @ YesterdayDateTime 跨度>); - 减去一毫秒
你不能:SQL中的DATETIME类型不存储为毫秒,它存储为自特定时间点以来的一些滴答,并且滴答通常为31.250毫秒(但取决于计算机):@@TIMETICKS [ ^ ]
i need date difference mill second
select DATEADD(Day, DATEDIFF(Day, 0, getdate()) - 1, -0.0000001)
2018-07-23 23:59:59.993
but i need as 2018-07-23 23:59:59.999
What I have tried:
I have tried as added zero
select DATEADD(Day, DATEDIFF(Day, 0, getdate()) - 1, -0.00000001)
2018-07-24 00:00:00.000
but i excepted as
2018-07-23 23:59:59.999
解决方案
Assuming SQL 2008 or higher, you'll need to use the new datetime2(7) type[^].
SELECT DateAdd(millisecond, -1, Cast(DateAdd(day, -1, Cast(SysDateTime() As date)) As datetime2(7))); -- Output: 2018-07-23 23:59:59.9990000
Breaking that down:
DECLARE @Now datetime2(7) = SysDateTime(); -- Get the current date and time DECLARE @Today date = Cast(@Now As date); -- Remove the time part DECLARE @Yesterday date = DateAdd(day, -1, @Today); -- Subtract one day DECLARE @YesterdayDateTime datetime2(7) = Cast(@Yesterday As datetime2(7)); -- Need a datetime2 to subtract time units DECLARE @Result datetime2(7) = DateAdd(millisecond, -1, @YesterdayDateTime); -- Subtract one millisecond
You can't: the DATETIME type in SQL is not stored as milliseconds, it is stored as a number of ticks since a specific point in time, and a tick is normally 31.250 milliseconds (but is computer dependant): @@TIMETICKS[^]
这篇关于如何日期差毫秒的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文