gpt4 book ai didi

tsql - 如何在日期时间中支持bigint

转载 作者:行者123 更新时间:2023-12-03 04:34:57 25 4
gpt4 key购买 nike

datediff(ss, '01/01/1970', '12/31/2050') 的结果是 bigint 所以 datediff 溢出。

如果 SQL 中日期的最大值为 int,如何从 bigint 值获取其等效日期并再次返回?

我需要能够在 SQL 中存储 01/01/197012/31/2050 之间的秒数(我将其作为 char),但将该值转换为其日历日期以在网页中显示。

任何想法将不胜感激。

谢谢!

最佳答案

这将为您提供毫秒数。轻松适应秒...

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>

关于tsql - 如何在日期时间中支持bigint,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/5874913/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com