如果未传入日期时间，则默认为上周日，考虑客户端时区

发布于 2025-01-09 05:22:58 字数 1084 浏览 0 评论 0原文

我有一个具有 @from 和 @to 日期输入参数的过程。这两种类型的数据类型都是DateTime。该过程可以从任何时区调用，并且数据库中的值以 UTC 存储。应用程序将客户端时间转换为 UTC 并调用该过程。

如果输入参数中没有发送起始日期，那么我必须将其默认为上周日。

这是我目前的逻辑，

Declare @currentDate Date = getdate()
Declare @fromDate DateTime = Null

Set @fromDate = Coalesce(@fromDate, DATEADD(wk, DATEDIFF(wk, 0, @currentDate), -1))

Select @fromDate

除了少数情况外，这在大多数情况下都有效：
如果我是
的用户 --EST 时区,
-- 在2022 年 2 月 26 日美国东部时间晚上 10 点 运行该程序，
--我为@from date 传递一个空值。

上面的代码如下所示：

Declare @currentDate Date = '27-Feb-2022 3:00:00' --UTC
Declare @fromDate DateTime = Null

Set @fromDate = Coalesce(@fromDate, DATEADD(wk, DATEDIFF(wk, 0, @currentDate), -1))

Select @fromDate

结果为 27-Feb-2022 00:00:00 UTC，转换为 EST 后将为 26-Feb-2022 19:00： 00。但由于我的日期必须默认为上周日，从技术上讲，它应该是 2022 年 2 月 20 日 00:00:00 EST，即 2022 年 2 月 20 日 5:00:00 UTC代码>.我对如何实现这一目标感到非常困惑，因为我在进行计算时必须考虑客户端时区，而且它可以是任何东西。我希望能得到一些关于如何实现这一目标的见解。谢谢你！

原文

I have a procedure that has @from and @to date input parameters. The data type for both these types is DateTime. This procedure can be called in from any time zone and the values in the DB are stored in UTC. The application converts the client side time to UTC and calls the procedure.

If no from date is sent in the input parameter then I have to default it to last Sunday.

Here's the current logic that I have

Declare @currentDate Date = getdate()
Declare @fromDate DateTime = Null

Set @fromDate = Coalesce(@fromDate, DATEADD(wk, DATEDIFF(wk, 0, @currentDate), -1))

Select @fromDate

This works well in most situations except a few:
If I'm a user in
--EST timezone,
--running the procedure at 10PM EST on 26-Feb-2022 and,
--I'm passing a null value for @from date.

The code above would be something like this:

Declare @currentDate Date = '27-Feb-2022 3:00:00' --UTC
Declare @fromDate DateTime = Null

Set @fromDate = Coalesce(@fromDate, DATEADD(wk, DATEDIFF(wk, 0, @currentDate), -1))

Select @fromDate

The result would be 27-Feb-2022 00:00:00 UTC which when translated to EST would be 26-Feb-2022 19:00:00. But since my date has to default to last Sunday it should technically be 20-Feb-2022 00:00:00 EST which is 20-Feb-2022 5:00:00 UTC. I'm very confused as to how to achieve this since I have to consider the client time zones when doing the calculations and it can be anything. I would appreciate some insights into how I can achieve this. Thank you!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

宫墨修音 2025-01-16 05:22:58

您可以在 SQL 端进行转换。如果您将声明更改为：

Declare @currentDate Date = cast('27-Feb-2022 3:00:00' as datetime) 
    at time zone 'utc'
    at time zone 'eastern standard time';

一切都应该按您的预期进行。为了说明这一点，您明确告诉 SQL 日期时间位于 UTC TZ 中，第一个时区，然后将其转换为第二个时区的 EST。

You can do the conversion SQL side. If you change your declaration to:

Declare @currentDate Date = cast('27-Feb-2022 3:00:00' as datetime) 
    at time zone 'utc'
    at time zone 'eastern standard time';

everything should work as you expect. To talk through it, you're explicitly telling SQL that the date time is in the UTC TZ with the first at time zone and then converting it to EST with the second.

回复收藏 0 原文

~没有更多了~