去年这个月的第一天和最后一天

I'm declaring some months and dates and need help on getting the first and last date of this month last year.

我宣布了一些月份和日期,需要帮助才能获得去年这个月的第一个和最后一个日期。

So far I have the following.

到目前为止,我有以下内容。

DECLARE @today date Set @today = cast (getdate() as date)
DECLARE @firstdaylastmonth date Set @firstdaylastmonth = DATEADD(month, DATEDIFF(month, 0, @today)-1, 0)
DECLARE @lastdaylastmonth date SET @lastdaylastmonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today),0))
DECLARE @lastdaythismonth date SEt @lastdaythismonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today)+1,0)) 

-- DECLARE @firstdaythismonthlastyear SET 
-- DECLARE @lastdaythismonthlastyear SET


Select 
@today,
@firstdaylastmonth,
@lastdaylastmonth,
@lastdaythismonth

-- @firstdaythismonthlastyear
-- @lastdaythismonthlastyear

But am having trouble finding the last 2 declarations. Help appreciated.

但我很难找到最后的2个声明。帮助赞赏。

4 个解决方案

#1

You can use the EOMONTH ( start_date [, month_to_add ] ) Function For SQL Server 2012 and above.

您可以使用EOMONTH(start_date [,month_to_add])函数用于SQL Server 2012及更高版本。

#2

This does what you asked for

这就是你要求的

DECLARE @firstdaythismonthlastyear date 
SET @firstdaythismonthlastyear = DATEADD(year,-1,DATEADD(month, DATEDIFF(month, 0, @today), 0))
DECLARE @lastdaythismonthlastyear date 
SET @lastdaythismonthlastyear = DATEADD(year,-1,@lastdaythismonth)

The first builds on what you had done for @firstdaylastmonth and the second uses your precalculated @lastdaythismonth.

第一个基于你为@firstdaylastmonth所做的事情,第二个使用你预先计算的@lastdaythismonth。

#3

You can use dateadd to offset by 12 months:

您可以使用dateadd抵消12个月:

DECLARE @mydate DATETIME
SELECT @mydate = GETDATE()
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)),@mydate),101)) ,
'Last Day of Previous Month, Last Year'
UNION
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(@mydate)-1),@mydate),101)) AS Date_Value,
'First Day of Current Month, Last Year' AS Date_Type
UNION
SELECT CONVERT(VARCHAR(25),@mydate,101) AS Date_Value, 'Today' AS Date_Type
UNION
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))),DATEADD(mm,1,@mydate)),101)) ,
'Last Day of Current Month, Last Year'
UNION
SELECT DATEADD(M,-12,CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@mydate))-1),DATEADD(mm,1,@mydate)),101)) ,
'First Day of Next Month, Last Year'

#4

We can CAST the first day of current month into a date and subtract one year for @firstdaythismonthlastyear, and just subtract a year from @lastdaythismonth to get @lastdaythismonthlastyear.

我们可以将当月的第一天写入一个日期,并为@firstdaythismonthlastyear减去一年,然后从@lastdaythismonth减去一年以获得@lastdaythismonthlastyear。

DECLARE @today date Set @today = cast (getdate() as date)
DECLARE @firstdaylastmonth date Set @firstdaylastmonth = DATEADD(month, DATEDIFF(month, 0, @today)-1, 0)
DECLARE @lastdaylastmonth date SET @lastdaylastmonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today),0))
DECLARE @lastdaythismonth date SEt @lastdaythismonth = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@today)+1,0)) 

DECLARE @firstdaythismonthlastyear date SET @firstdaythismonthlastyear = DATEADD(year, -1,CAST(CONCAT(YEAR(@today),'-',MONTH(@today),'-',1) as date))
DECLARE @lastdaythismonthlastyear date SET @lastdaythismonthlastyear = DATEADD(year, -1, @lastdaythismonth)

Select 
@today,
@firstdaylastmonth,
@lastdaylastmonth,
@lastdaythismonth,
@firstdaythismonthlastyear,
@lastdaythismonthlastyear

#1