SQL Server:计算日期范围 [英] SQL Server: calculating date ranges

问题描述

我正在尝试获取从本月初(上一年)到当天(上一年)的日期范围.有没有办法简化这段代码?

I'm trying to get a date range from the beginning of this month (previous year) to current day (previous year). Is there a way to simplify this code?

SELECT 'From the beginning of month to this day, previous year' AS Label,
       CONVERT (DATETIME, CAST (DATEPART(YY, GETDATE()) - 1 AS CHAR (4)) + CASE 
                                                                           WHEN DATEPART(MM, GETDATE()) < 10 THEN '-0' + CAST (DATEPART(MM, GETDATE()) AS CHAR (1)) ELSE '-' + CAST (DATEPART(MM, GETDATE()) AS CHAR (2)) 
                                                                           END + '-01') AS Begin_date,
       CONVERT (DATETIME, CAST (DATEPART(YY, GETDATE()) - 1 AS CHAR (4)) + CASE 
                                                                           WHEN DATEPART(MM, GETDATE()) < 10 THEN '-0' + CAST (DATEPART(MM, GETDATE()) AS CHAR (1)) ELSE '-' + CAST (DATEPART(MM, GETDATE()) AS CHAR (2)) 
                                                                           END + CASE 
                                                                                 WHEN DATEPART(DD, GETDATE()) < 10 THEN '-0' + CAST (DATEPART(DD, GETDATE()) AS CHAR (1)) ELSE '-' + CAST (DATEPART(DD, GETDATE()) AS CHAR (2)) 
                                                                                 END) AS End_date

推荐答案

SELECT  'Anything' as Label
        ,DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) as firstdaythismonth
        ,DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0) as today
        ,DATEADD(year, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) as firstdaythismonth_lastyear
        ,DATEADD(year, -1, DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)) as today_lastyear

这篇关于SQL Server:计算日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！