如何获取当前年份的第一个和最后一个日期? [英] How to get the first and last date of the current year?

问题描述

使用 SQL Server 2000，如何获取当前年份的第一个和最后一个日期?

Using SQL Server 2000, how can I get the first and last date of the current year?

预期输出:

01/01/2012 和 31/12/2012

推荐答案

SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear

上述查询给出了 12 月 31 日开始的午夜的日期时间值.这比一年中的最后时刻少了大约 24 小时.如果您想包括可能发生在 12 月 31 日的时间，那么您应该与下一年的第一天进行比较，并使用 < 比较.或者，您可以与当前年份的最后几毫秒进行比较，但如果您使用的不是 DATETIME(例如 DATETIME2)，这仍然会留下一个差距:

The above query gives a datetime value for midnight at the beginning of December 31. This is about 24 hours short of the last moment of the year. If you want to include time that might occur on December 31, then you should compare to the first of the next year, with a < comparison. Or you can compare to the last few milliseconds of the current year, but this still leaves a gap if you are using something other than DATETIME (such as DATETIME2):

SELECT
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS LastDayOfYear,
   DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0) AS FirstOfNextYear,
   DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, 0)) AS LastTimeOfYear

其他时期

这种方法有两个不错的方面:良好的性能，并且可以通过用不同的字符串替换出现的 yy (=year) 来轻松更改其他时间段:

This approach has two nice aspects: good performance and it can easily be changed for other periods by replacing both occurrences of yy (=year) with a different string:

yy, yyyy    year
qq, q       quarter
mm, m       month
wk, ww      week 

(注意几周:开始日期取决于服务器设置.)

(Be careful of weeks: the starting day depends on server settings.)

技术详情

这是通过使用 DATEDIFF(yy, 0, GETDATE()) 算出自 1900 年以来的年数，然后将其添加到零日期 = 1900 年 1 月 1 日.这可以是通过将 GETDATE() 部分替换为任意日期，或者通过将 DATEDIFF(...) 函数替换为Year - 1900"来更改为适用于任意日期.;

This works by figuring out the number of years since 1900 with DATEDIFF(yy, 0, GETDATE()) and then adding that to a date of zero = Jan 1, 1900. This can be changed to work for an arbitrary date by replacing the GETDATE() portion or an arbitrary year by replacing the DATEDIFF(...) function with "Year - 1900."

 SELECT
   DATEADD(yy, DATEDIFF(yy, 0, '20150301'), 0) AS StartOfYearForMarch2015,
   DATEADD(yy, 2015 - 1900, 0) AS StartOfYearFor2015

这篇关于如何获取当前年份的第一个和最后一个日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！