Setting the dates

Transact-SQL
1

I need to set the start date and end date . It should begin on the first of the month and incremented till the first sunday and for the last week of the month the end date should be the last day of the month.
Thanks

2

I didn't quite understand your question. If you want to find the first date of the current month:

DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)

If you want to find the last day of the month

DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())+1,-1)

What I did not understand what you described about incrementing and finding the first sunday. Can you explain with some sample data?

3

I need to run a package ever week starting from the first of month for example consider the following dates for November:

2015-11-01 2015-11-08
2015-11-09 2015-11-15
2015-11-16 2015-11-22
2015-11-23 2015-11-30
Usually run every Monday but for the last week it should fall on Month end
So I am looking for way to set my start date and end date

Thanks

4

To clarify the rules a bit, what would the dates be for December 2015?

5

You can do something like this:

DECLARE @DATE DATE = GETDATE()
DECLARE @FDM DATE
SET @FDM = DATEADD( MM, DATEDIFF ( MM, 0, @DATE), 0)

SELECT @FDM AS FIRST_DAY_MONTH
,DATEADD(DD, 8 - DATEPART(DW, @FDM), @FDM) FIRST_SUNDAY