SQL Server Scenario Based Interview Question for Date

1.  How to get current month first date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current DateSelect @v_CurrentDate
-- Current month first date
Select DATEADD(DD,1,EOMONTH(@v_CurrentDate,-1))

Select DATEADD(DD,-DAY(@v_CurrentDate) + 1,CAST(getdate() as date))

Output:

2014-04-01

2. How to get current month last date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current Date
Select @v_CurrentDate

-- Current month last date
Select EOMONTH(@v_CurrentDate)

Select DATEADD(DD,-DAY(DATEADD(MM,1,@v_CurrentDate)),CAST(DATEADD(MM,1,@v_CurrentDate) as date))

Output:

2014-04-30

3. How to get next month first date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current Date
Select @v_CurrentDate

-- Next month first date
Select DATEADD(DD,1,EOMONTH(@v_CurrentDate))

Select DATEADD(DD,-DAY(DATEADD(MM,1,@v_CurrentDate)) + 1,CAST(DATEADD(MM,1,@v_CurrentDate) as date))

Output:

2014-05-01

4. How to get next month last date?

Query:

Declare @v_CurrentDate date = getdate()

-- Current Date
Select @v_CurrentDate

-- Next month last date
Select EOMONTH(@v_CurrentDate,1)

Select DATEADD(DD,-DAY(DATEADD(MM,2,@v_CurrentDate)),CAST(DATEADD(MM,2,@v_CurrentDate) as date))

Output:

2014-05-31

Please feel free suggest if you have more question. We will include and also give credit for that.