SQL for current Month and week

Question

I need SQL for getting current month start and end (1 of current month to 28/30/31 of current month) in yyyyMMdd format and for week too, but week should be from Sunday to Saturday

I used for week start date

SELECT CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, 0,   SYSDATETIME()), 0), 112)

for week end date

SELECT CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(WEEK, DATEDIFF(WEEK, 0, SYSDATETIME()) + 1, 0), 112))

For month start

SELECT CONVERT(INT, CONVERT(VARCHAR(6), SYSDATETIME(), 112) + '01')

for month end

SELECT CONVERT(INT, CONVERT(VARCHAR(6), DATEADD(MONTH, 1, SYSDATETIME()), 112) + '01')

But issue is week is giving me week date from Monday to Monday and month end date is giving me end date for start of next month (20160801)

The result should be

Week from 20160710 to 20160716
Month from 20160701 to 20160731

Please help me correct it.

Answer 1

--Start Day of month

select replace(cast(dateadd(mm,datediff(mm,0,getdate()),0) as date),'-','')

--End Day of the month

select replace(cast(dateadd(mm,datediff(mm,0,getdate())+1,0)-1 as date),'-','')

-- In SQL 2012 and higher

select replace(eomonth(getdate()),'-','');

--Start Day of the week

select replace(cast(dateadd(ww,datediff(ww,0,getdate()),0)-1 as date),'-','')

--End Day of the week

select replace(cast(dateadd(ww,datediff(ww,0,getdate())+1,0)-2 as date),'-','')

Answer 2

How about using DATEPART, DateAdd and DateDiff functions:

SELECT CONVERT(INT, CONVERT(VARCHAR(8),  DateAdd(d, 7 - DatePart(dw, getdate()), getdate()), 112))

and for the month:

SELECT CONVERT(INT, CONVERT(VARCHAR(8), DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)), 112))

Answer 3

The first day of the week is controlled by DATEFIRST. Here's how to calculate the first day of the week respecting the current DATEFIRST setting, and also the first and last day of the current month.

SET DATEFIRST 7 --Sunday
-- Start/End of Weeks respecting DATEFIRST
SELECT 
  DATEADD(DAY,  1-DATEPART(WEEKDAY, GETDATE()), GETDATE()) FirstDayOfCurrentWeek,
  DATEADD(DAY,  7-DATEPART(WEEKDAY, GETDATE()), GETDATE()) LastDayOfCurrentWeek,
  DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0) FirstDayOfMonth,
  DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, 0)) LastDayOfMonth

To convert dates to ISO 8601 use

CONVERT(INT, CONVERT( VARCHAR, SomeDate, 112 ))