SQL 如何获取当前年份的第一个和最后一个日期？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13437362/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the first and last date of the current year?
提问by Gopal
Using SQL Server 2000, how can I get the first and last date of the current year?
使用 SQL Server 2000，如何获取当前年份的第一个和最后一个日期？
Expected Output:
预期输出：
01/01/2012and 31/12/2012
01/01/2012和 31/12/2012
回答by Jamie F
SELECT
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AS StartOfYear,
DATEADD(yy, DATEDIFF(yy, 0, GETDATE()) + 1, -1) AS EndOfYear
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):
上面的查询给出了 12 月 31 日开始的午夜的日期时间值。这比一年中的最后时刻少了大约 24 小时。如果要包括可能发生在 12 月 31 日的时间，则应与下一年的第一天进行<比较，并进行比较。或者您可以与当前年份的最后几毫秒进行比较，但如果您使用的不是 DATETIME（例如 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
Tech Details
技术详情
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."
这是通过计算自 1900 年以来的年数，DATEDIFF(yy, 0, GETDATE())然后将其添加到零日期 = 1900 年 1 月 1 日。这可以通过替换GETDATE()部分或任意年份来更改为适用于任意日期，方法是将DATEDIFF(...)函数替换为“年 - 1900 年。”
SELECT
DATEADD(yy, DATEDIFF(yy, 0, '20150301'), 0) AS StartOfYearForMarch2015,
DATEADD(yy, 2015 - 1900, 0) AS StartOfYearFor2015
回答by Vikdor
You can get the current year using DATEPARTfunction, from the current date obtained using getUTCDate()
您可以使用DATEPART函数获取当前年份，从使用获得的当前日期开始getUTCDate()
SELECT
'01/01/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate())),
'31/12/' + CONVERT(VARCHAR(4), DATEPART(yy, getUTCDate()))
回答by Aubrey Love
Here's a fairly simple way;
这是一个相当简单的方法；
SELECT DATEFROMPARTS(YEAR(GETDATE()), 1, 1) AS 'First Day of Current Year';
SELECT DATEFROMPARTS(YEAR(GETDATE()), 12, 31) AS 'End of Current Year';
It's not sexy, but it works.
这不是性感，但它有效。
回答by Pradeep atkari
simply write:-
简单地写：-
select convert (date,DATEADD(YEAR,DATEDIFF(YEAR,0,GETDATE()),0))
start date of the year.
年的开始日期。
select convert (date,DATEADD(YEAR, DATEDIFF(YEAR,0,GETDATE()) + 1, -1))
回答by Rahul Tripathi
Every year has the 1 st as First date and 31 as the last date what you have to do is only attach the year to that day and month for example:-
每年的第一个日期是第一个日期，最后一个日期是 31，您只需将年份附加到该日期和月份，例如：-
SELECT '01/01/'+cast(year(getdate()) as varchar(4)) as [First Day],
'12/31/'+cast(year(getdate()) as varchar(4)) as [Last Day]
回答by Gayle
To get the first and the last day of the year, one can use the CONCATfunction. The resulting value may be cast to any type.
要获得一年的第一天和最后一天，可以使用该CONCAT函数。结果值可以转换为任何类型。
CONCAT(YEAR(Getdate()),'-01-01') FirstOfYear,
CONCAT(YEAR(GETDATE()),'-12-31') LastOfYear
回答by EstevaoLuis
Another way: (Since SQL Server 2012)
另一种方式：（自 SQL Server 2012 起）
SELECT
DATEFROMPARTS(YEAR(GETDATE()), 1, 1) FirstDay,
DATEFROMPARTS(YEAR(GETDATE()),12,31) LastDay
回答by DHANA LAKSHMI
For start date of current year:
对于当年的开始日期：
SELECT DATEADD(DD,-DATEPART(DY,GETDATE())+1,GETDATE())
For end date of current year:
对于当年的结束日期：
SELECT DATEADD(DD,-1,DATEADD(YY,DATEDIFF(YY,0,GETDATE())+1,0))
回答by zadeveloper
print Cast('1/1/' + cast(datepart(yyyy, getdate()) as nvarchar(4)) as date)
回答by chjortlund
It looks likeyou are interesting in performing an operation everything for a given year, if this is indeed the case, I would recommend to use the YEAR()function like this:
看起来您对给定年份的所有操作都很感兴趣，如果确实如此，我建议您使用YEAR()函数，如下所示：
SELECT * FROM `table` WHERE YEAR(date_column) = '2012';
The same goes for DAY()and MONTH(). They are also available for MySQL/MariaDB variants and was introduced in SQL Server 2008 (so not for specific 2000).
这同样适用于DAY（）和MONTH（） 。它们也可用于 MySQL/MariaDB 变体，并在 SQL Server 2008 中引入（因此不适用于特定的 2000）。
