CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Aiden
Aiden

Reputation: 460

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.

Upvotes: 1

Views: 1353

Answers (3)

Liesel
Liesel

Reputation: 2979

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

Upvotes: 1

Steve Ford
Steve Ford

Reputation: 7753

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

Upvotes: 1

knkarthick24
knkarthick24

Reputation: 3216

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

Upvotes: 1

Related Questions