如何在日期时间支持 bigint [英] How to support bigint in datetime
本文介绍了如何在日期时间支持 bigint的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
datediff(ss, '01/01/1970', '12/31/2050')
的结果是一个 bigint
所以 datediff代码> 溢出.
The result of datediff(ss, '01/01/1970', '12/31/2050')
is a bigint
so datediff
is overflowing.
如果 SQL 中的最大日期是 int
,我如何从 bigint
值获取到其等效日期并再次返回?
How can I get from a bigint
value to its equivalent date and back again if the max a date can be in SQL is int
?
我需要能够在 SQL 中存储 01/01/1970
和 12/31/2050
之间的秒数(我作为 char
) 但将该值转换为其日历日期以在网页中显示.
I need to be able to store number of seconds between 01/01/1970
and 12/31/2050
in SQL (which I do as a char
) but convert that value to its calendar date for display in a web page.
任何想法将不胜感激.
谢谢!
推荐答案
这会给你几毫秒的时间.容易适应秒...
This will give you miliseconds. Easy to adapt to seconds...
declare @dfrom datetime = '1970-01-01 16:15:33.021'
declare @dto datetime = '2058-01-01 15:00:55.557'
declare @diff bigint =
cast(DATEDIFF(d, @dfrom, @dto) as bigint) * 24 * 3600 * 1000
+ DATEDIFF(ms, cast(@dfrom as time), cast(@dto as time))
declare @dreverse datetime =
dateadd(ms, @diff % (1000 * 3600 * 24),
dateadd(day, @diff / (1000 * 3600 * 24), @dfrom))
select @dfrom as [From], @dto as [To], @diff as [Diff], @dreverse as [Reverse] for xml path('')
给出:
<From>1970-01-01T16:15:33.020</From>
<To>2058-01-01T15:00:55.557</To>
<Diff>2777064322537</Diff>
<Reverse>2058-01-01T15:00:55.557</Reverse>
这篇关于如何在日期时间支持 bigint的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文