【SQL】How to get First and Last day of a Year – TSQL

最新推荐文章于 2021-04-23 14:01:03 发布

weixin_33712881

最新推荐文章于 2021-04-23 14:01:03 发布

阅读量68

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/taoqianbao/archive/2012/12/27/2835384.html

本文展示了如何使用SQL查询生成当前年份、上一年及下一年的起始和结束日期，包括具体日期和毫秒级时间戳。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, -1, GETDATE())), 0),
            'First Day of Previous Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3, DATEADD(YEAR,
            DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)),
            'Last Day of Previous Year'
UNION ALL
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),
            'First Day of Current Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)),
            'Last Day of Current Year'
UNION ALL
SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR,1,GETDATE())), 0),
            'First Day of Next Year'
UNION ALL
SELECT      DATEADD(MILLISECOND, -3,
            DATEADD(YEAR, DATEDIFF(YEAR, 0,
            DATEADD(YEAR, 1, GETDATE())) + 1, 0)),
            'Last Day of Next Year'

Result Set:

———————– ————————–
2010-01-01 00:00:00.000 First Day of Previous Year
2010-12-31 23:59:59.997 Last Day of Previous Year
2011-01-01 00:00:00.000 First Day of Current Year
2011-12-31 23:59:59.997 Last Day of Current Year
2012-01-01 00:00:00.000 First Day of Next Year
2012-12-31 23:59:59.997 Last Day of Next Year

(6 row(s) affected)

These can be generalized as:

DECLARE @TRAVELYEARS INT = -1
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0), 'First Day of Year'
UNION ALL
SELECT DATEADD(MILLISECOND, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)), 'Last Day of Year'

SET @TRAVELYEARS = 1
SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0), 'First Day of Year'
UNION ALL
SELECT DATEADD(MILLISECOND, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)), 'Last Day of Year'

Result Set:

———————– ———————-
2010-01-01 00:00:00.000 First Day of Next Year
2010-12-31 23:59:59.997 Last Day of Next Year

(2 row(s) affected)

———————– ———————-
2012-01-01 00:00:00.000 First Day of Next Year
2012-12-31 23:59:59.997 Last Day of Next Year

(2 row(s) affected)

Hope This Helps!

转载于:https://www.cnblogs.com/taoqianbao/archive/2012/12/27/2835384.html