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

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

阅读量：5758 次

发布时间：2019-06-18

本文共 2167 字，大约阅读时间需要 7 分钟。

SELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,            DATEADD(YEAR, -1, GETDATE())), 0),            'First Day of Previous Year'UNION ALLSELECT      DATEADD(MILLISECOND, -3, DATEADD(YEAR,            DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())) + 1, 0)),            'Last Day of Previous Year'UNION ALLSELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()), 0),            'First Day of Current Year'UNION ALLSELECT      DATEADD(MILLISECOND, -3,            DATEADD(YEAR, DATEDIFF(YEAR, 0, GETDATE()) + 1, 0)),            'Last Day of Current Year'UNION ALLSELECT      DATEADD(YEAR, DATEDIFF(YEAR, 0,            DATEADD(YEAR,1,GETDATE())), 0),            'First Day of Next Year'UNION ALLSELECT      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 = -1SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0), 'First Day of Year'UNION ALLSELECT DATEADD(MILLISECOND, -3, DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS, GETDATE())) + 1, 0)), 'Last Day of Year'SET @TRAVELYEARS = 1SELECT DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, @TRAVELYEARS ,GETDATE())), 0), 'First Day of Year'UNION ALLSELECT 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

你可能感兴趣的文章