解释这些日期的逻辑,这些日期用于获取上一年的第一天和最后一天。

huangapple go评论57阅读模式
英文:

Explain the logic of these dates which are used to get the previous year first and last dates

问题

以下是您提供的SQL代码的翻译部分:

DECLARE @FromTestDate Datetime
DECLARE @ToTestDate Datetime
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @FromTestDate= DATEDIFF(yy,0,GETDATE())
SELECT @FromTestDate AS FromTestDate

SET @FromDate = DATEADD(yy, datediff(yy, 0, GETDATE())-1, 0) 
--@FromDate应该是(CurrentDate + From TestDate -- 3800-06-04)
SELECT @FromDate As FromDate 

SET @ToTestDate= DATEDIFF(yy,30,GETDATE())
SELECT @ToTestDate AS ToTestDate

SET @ToDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), -1)   
SELECT @ToDate AS ToDate

请注意,这段SQL代码用于计算上一年的第一天和最后一天的逻辑。对于@FromDate的计算,您提到它应该是(当前日期 + FromTestDate -- 3800-06-04),但实际上它是根据从当前日期减去一年来计算的。这是因为DATEADD函数的第二个参数是负数,表示减去指定的时间间隔。所以它将当前日期减去一年,而不是加上FromTestDate

希望这能帮助您理解代码的逻辑。

英文:
DECLARE @FromTestDate Datetime
DECLARE @ToTestDate Datetime
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME

SET @FromTestDate= DATEDIFF(yy,0,GETDATE())
SELECT @FromTestDate AS FromTestDate

SET @FromDate = DATEADD(yy, datediff(yy, 0, GETDATE())-1, 0) 
--@FromDate should be (CurrentDate + From TestDate -- 3800-06-04)
SELECT @FromDate As FromDate 

SET @ToTestDate= DATEDIFF(yy,30,GETDATE())
SELECT @ToTestDate AS ToTestDate

SET @ToDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), -1)   
SELECT @ToDate AS ToDate

This is the logic we use to calculate the previous year first and last dates. I am new to SQL and as per dateadd() functionality it would add the dates and @FromDate should be (CurrentDate + From TestDate -- 3800-06-04) but it is behaving differently and providing what is expected. I just want to understand the logic behind it.

Expecting the analysis

答案1

得分: 5

我不知道是否有人可以(或者应该)反向工程这个混乱的代码。这些事情要表达得简单得多。你不需要测试任何东西,提出任意日期等等。

-- 如果我们只把上一年存入一个变量中:

DECLARE @LastYear int = DATEPART(YEAR, getdate()) - 1;

-- 上一年的第一天是:

SELECT FromDate = DATEFROMPARTS(@LastYear, 1, 1);

-- 上一年的最后一天是:

SELECT ToDate = DATEFROMPARTS(@LastYear, 12, 31));

所有这些从零开始的datediff无聊的事情是我们在SQL Server 2005中不得不做的事情,但我们不应该再这样做了。而且使用yy而不是拼写YEAR只是...为了好玩而神秘吗?请阅读负责任地约会中的所有链接。

无论如何,回答所提出的问题(“请解释这个混乱”):

DECLARE @FromTestDate Datetime
DECLARE @ToTestDate Datetime
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
SET @FromTestDate= DATEDIFF(yy,0,GETDATE())

这是不对的。在右边,我们计算yy(更常被称为year)在0(这是一种懒惰/神秘的表示1900-01-01)和今天之间的差异。这个答案至少在今年的剩下时间里会产生123。但当我们将它赋给一个datetime时,我们实际上是在说“在0上加123天”(再次强调,这是1900-01-01):

DECLARE @d datetime = 123;
SELECT @d;

-- 结果:
1900-05-04 00:00:00.000

1900-01-01之后的123天是同年的5月4日。

提示:从0{now}的datediff仅适用于确定天数的日期时间,如果你以年为单位计算datediff,例如,它只能用来表示已经过去的年数。重点是,你不能将年数赋给日期时间并期望它产生正确的日期时间,因为它会将你打算表示的“年”数加到1900-01-01上。

接下来是:

SET @FromDate = DATEADD(yy, datediff(yy, 0, GETDATE())-1, 0)

这意味着(再次强调,是不正确的)“计算01900-01-01)和今天之间的年份差,然后减去1,然后将这么多年加到01900-01-01)上”。这实际上可以工作,因为我们在使用输出(一个整数,而不是日期时间)将年数添加到另一个日期时。但正如我上面所描述的,这是一种非常混乱的表达方式。

--@FromDate应该是(当前日期 + FromTestDate -- 3800-06-04)

我不确定这个注释是什么意思。将今天加到今天不会使今天加倍并在4046年的四月某个时候。所以不确定你是怎么得到3800年的。

接下来:

SET @ToTestDate= DATEDIFF(yy,30,GETDATE())

好吧,这没有意义。我们再次尝试将一个整数值(试图在今天添加30年,我不确定)分配给日期时间。如书面所述,实际上表示“从301900-01-01之后30天)到今天过去了多少年”。分步显示它:

DECLARE @i int = DATEDIFF(yy, 30, GETDATE());
SELECT @i; -- 123

DECLARE @d datetime = DATEDIFF(yy, 30, GETDATE());
SELECT @d; -- 1900-05-04 00:00:00.000

-- 这等同于
DECLARE @x datetime = 123;

最后:

SET @ToDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), -1)

这个更加神秘,有趣的巧合是,它实际上是可以工作的。这意味着将从01900-01-01)到今天的年数添加到-1-1是,你可能已经猜到,1900-01-01之前的一天,1899-12-31。所以,将从1900年起过去的年数加到1900年1月1日之前的一天,只是“恰好”可以工作,尽管我认为作者预期它会工作,因为他们认为他们是在表示“减去比年数少一年”的年数。

英文:

I have no idea if anyone can (or should) reverse engineer that spaghetti. These things are so much easier to express. You don't need to test anything, come up with arbitrary dates, etc.

-- If we just take *last* year into a variable:

DECLARE @LastYear int = DATEPART(YEAR, getdate()) - 1;

-- First day of last year is:

SELECT FromDate = DATEFROMPARTS(@LastYear, 1, 1);

-- Last day of last year is:

SELECT ToDate = DATEFROMPARTS(@LastYear, 12, 31));

All this datediff from zero nonsense is the kind of thing we had to do in SQL Server 2005, but we shouldn't be doing it anymore. And using yy instead of spelling out YEAR is just... being cryptic for fun? Please read through all the links at Dating Responsibly.


Anyway, to answer the question asked ("please explain this spaghetti"):

DECLARE @FromTestDate Datetime
DECLARE @ToTestDate Datetime
DECLARE @FromDate DATETIME
DECLARE @ToDate DATETIME
  SET @FromTestDate= DATEDIFF(yy,0,GETDATE())

This is just wrong. On the right-hand side we calculate the difference in yys (better known as years) between 0 (which is a lazy/cryptic way to express 1900-01-01). This answer yields, well, for the rest of this year at least, 123. When we assign that to a datetime, though, we are saying "add 123 days to 0" (which, again, is 1900-01-01):

DECLARE @d datetime = 123;
SELECT @d;

-- result:
1900-05-04 00:00:00.000

123 days after 1900-01-01 is May the 4th be with you of that same year.

Tip: Taking the datediff from 0 to {now} only works for determining a datetime if you are taking the datediff in days. If you are taking the datediff in years, for example, it can only be used to indicate the number of years that have passed.<sup>*</sup> Point is, you can't assign a number of years to a datetime and expect it to yield the right datetime, because it will take that number that you intend to mean years and add that many days to 1900-01-01.

Next we have:

SET @FromDate = DATEADD(yy, datediff(yy, 0, GETDATE())-1, 0) 

This is saying (again, incorrectly) "take the difference in years between 0 (1900-01-01) and today, subtract one, and then add that many years to 0 (1900-01-01). This actually works because we are using the output (an integer, not a datetime) to add years to another date. But as I describe above, this is a really messy way to express that.

  --@FromDate should be (CurrentDate + From TestDate -- 3800-06-04)

I'm not sure what this comment is supposed to mean. Adding today to today doesn't double today and make it sometime in April, 4046. So not sure how you got the year 3800 there.

Next:

  SET @ToTestDate= DATEDIFF(yy,30,GETDATE())

Well, this doesn't make sense. We're again trying to assign to a datetime an integer value that is attempting to, I'm not sure, maybe add 30 years to today? As written, this actually says "how many years have passed since 30 (which is 30 days after 0 (1900-01-01)). Showing it in steps:

DECLARE @i int = DATEDIFF(yy, 30, GETDATE());
SELECT @i; -- 123

DECLARE @d datetime = DATEDIFF(yy, 30, GETDATE());
SELECT @d; -- 1900-05-04 00:00:00.000

-- which is the same as
DECLARE @x datetime = 123;

Last:

  SET @ToDate = DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), -1) 

This one is even more cryptic and a funny coincidence that it actually works. This says take the number of years that have passed since 0 (1900-01-01), and add that many years to -1. -1 is, you may have guessed, one day before 1900-01-01, 1899-12-31. So adding the number of years that have passed since 1900 to the day before Jan 1, 1900 just happens to work out, when I think the author expected that to work because they thought they were saying "add one less year than the number of years that have passed."


<sup>*</sup> To be more accurate, the number of year boundaries that have passed. These yield the same answer (1), even though in one case almost two years have passed, and in the other, only one day has passed:

SELECT DATEDIFF(YEAR, &#39;20210101&#39;, &#39;20221231&#39;),
       DATEDIFF(YEAR, &#39;20211231&#39;, &#39;20220101&#39;);

huangapple
  • 本文由 发表于 2023年2月24日 03:28:43
  • 转载请务必保留本文链接:https://go.coder-hub.com/75549474.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定