CODEWITHSUNDEEP
sqlsql-servert-sql
mohan111
mohan111

Reputation: 8865

How to get First and Last for every month

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

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'

need output like this 

     Comments
     Last 4 DAYS of Nov

Suggest me the way to proceed

Upvotes: 0

Views: 112

Answers (4)

Lus
Lus

Reputation: 1

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

Upvotes: 0

Eralper
Eralper

Reputation: 6622

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.

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

Upvotes: 0

T.Q.H
T.Q.H

Reputation: 89

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)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

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

Upvotes: 4

Related Questions