如何获得每个月的第一个和最后一个

How to Add First for 1 to 10 dates for a month and Add Last for 20 to 30 days of month.

如何为一个月的1到10个日期添加第一个，为月的20到30天添加最后一个。

DECLARE @Today DATE = '2016-11-05'

Select  CONVERT(VARCHAR(5),datepart (DW, @Today)-1 )+' DAYS of '+ LEFT(DATENAME(month,@Today),3) Comments

I'm getting like this

我是这样的

Comments
6 DAYS of Nov

How to get like this :

怎么这样：

Comments
First 6 DAYS of Nov

if I give date as '2016-11-24'

如果我给'2016-11-24'的日期

need output like this

需要这样的输出

     Comments
     Last 4 DAYS of Nov

Suggest me the way to proceed

建议我继续前进的方式

4 个解决方案

#1

Use a case statement:

使用案例陈述：

Select (CASE WHEN day(@today) <= 10 THEN 'First '
             WHEN day(@today) >= 20 THEN 'Last '
             ELSE ''
        END) + CONVERT(VARCHAR(5), datepart(DW, @Today)-1 ) + ' DAYS of ' +
        LEFT(DATENAME(month, @Today), 3) as Comments

EDIT:

编辑：

Oh, now I see the original query was not right. So you want something more like this:

哦，现在我看到原始查询不对。所以你想要更像这样的东西：

Select (CASE WHEN day(@today) <= 10 THEN 'First ' + DATENAME(day, @today) + ' DAYS of ' + LEFT(DATENAME(month, @Today), 3)
             WHEN day(@today) >= 20 AND MONTH(@Today) IN (1, 3, 5, 7, 8, 10, 12) THEN 'Last ' + CAST(31 - day(@today) as varchar(255))
             WHEN day(@today) >= 20 AND MONTH(@Today) IN (4, 6, 9, 11) THEN 'Last ' + CAST(30 - day(@today) as varchar(255))
             WHEN day(@today) >= 20 AND MONTH(@Today) IN (2) AND YEAR(@Today) % 4 = 0 THEN 'Last ' + CAST(29 - day(@today) as varchar(255))
             WHEN day(@today) >= 20 AND MONTH(@Today) IN (2) AND YEAR(@Today) % 4 <> 0 THEN 'Last ' + CAST(29 - day(@today) as varchar(255))
             ELSE CAST(day(@today) as varchar(255))
        END)  + ' DAYS of ' + LEFT(DATENAME(month, @Today), 3) as Comments

#2

DECLARE @Today DATE = '2016-11-09'

Select (CASE WHEN day(@today) <= 10 THEN 'First ' + DATENAME(day, @today)
             WHEN day(@today) >= 20 THEN 'Last ' + CAST(DAY(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@Today)+1,0))) - day(@today) as varchar(255))
             ELSE CAST(day(@today) as varchar(255) )
        END)  + ' DAYS of ' + LEFT(DATENAME(month, @Today), 3)

#3

With SQL Server 2012 developers can now use new SQL date function EOMonth() for calculating the last date of the month where a given date is in.

使用SQL Server 2012，开发人员现在可以使用新的SQL日期函数EOMonth（）来计算给定日期所在月份的最后日期。

Here is my query

这是我的查询

--DECLARE @Today DATE = '2016-11-05'
DECLARE @Today DATE = '2016-11-24'

SELECT
 case when DATEPART(dd,@Today) <= 10 then
 'First ' + convert(varchar(2), DATEPART(dd,@Today)) + ' of ' + DATENAME(mm,@Today)
 else
 'Last ' + convert(varchar(2), DATEDIFF(dd, @Today, EOMONTH (@Today))) + ' of ' + DATENAME(mm,@Today)
 end

The SQL EOMonth() function makes it easier to calculate the last date of a month, and also indirectly calculating the first date of next month or previous month

SQL EOMonth（）函数可以更容易地计算一个月的最后日期，也可以间接计算下个月或上个月的第一个日期

#4

SELECT case when DATEPART(dd,@Today) <= 10 then
 'First ' + convert(varchar(2), DATEPART(dd,@Today)) + ' DAYS of ' + DATENAME(mm,@Today) 
WHEN DATEPART(dd,@Today) >= 20 then  'Last ' + convert(varchar(2),
DATEDIFF(dd, @Today, EOMONTH (@Today))) + ' DAYS of ' + DATENAME(mm,@Today) 
ELSE @Today END

#1