Aiden Aiden - 4 months ago 24
SQL Question

SQL for current Month and week

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

--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),'-','')