How to get firstdate and lastdate of eachmonth

Question

Using Sql Server 2000

Table1

id sdate edate
001 05/01/2012 25/02/2012
002 19/02/2012 17/04/2012
.....

sDate, eDate format is dd/mm/yyyy

I want to get firstdate and lastdate of eachmonth from the table1 like 01/01/2012 31/01/2012, 01/02/2012 29/02/2012

Expected Output

id sdate edate
001 05/01/2012 31/01/2012
001 01/02/2012 25/02/2012
002 19/02/2012 29/02/2012
002 01/03/2012 31/03/2012
002 01/04/2012 17/04/2012
.....

How to make a query for the above condition?

Need Query help

You will need to explain your logic more clearly, it isn't obvious (to me) how you expect to get the results from your data. This also seems to be related to [another of your questions](http://stackoverflow.com/questions/10796239/how-to-split-the-date-into-monthwise/10800749#10800749) that I answered, so you may want to review that, in particular the advice to create a calendar table and a numbers table. They will make questions like this a lot easier. — Pondlife, May 31 '12 at 06:45
i created calendar table like this 01/01/2012 ... 31/01/2012...29/01/2012..., then how can i get firstdate and lastdate of each month.... — JetJack, May 31 '12 at 06:50
@JetJack I have no idea what your calendar table looks like. In mine I would do something like `select BaseDate from dbo.Calendar where YearNumber = @Year and MonthNumber = @Month and IsLastDayOfMonth = 0x1` And the general topic of getting first/last days in a month has been discussed many times here so you should do some searching. Although the most common answers seem to use functions, whereas I personally find a calendar table solution much simpler and more obvious. — Pondlife, May 31 '12 at 07:14
Either you do not pay much attention to answers you get or you do not put enough effort in learning from them. My answer to the same question that @Pondlife has referred you to calculates both the first and the last days of months, which, even if not obvious from the code, should at least be apparent from the column names I used (MonthStart, MonthEnd). — Andriy M, May 31 '12 at 08:12

score 2 · Accepted Answer · answered May 31 '12 at 06:37

SELECT 
DATEADD(mm, DATEDIFF(mm, 0, sdate), 0),
DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, edate) + 1, 0))
from yourtable;

Its actually a two part query:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);
SELECT DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, GETDATE()) + 1, 0));

Above will give you start date and end date for the current month,

How to get firstdate and lastdate of eachmonth

1 Answers1