Calculate the First Day of Previous Week

Question

Calculate the First Day of Previous Week

Welsh Corgi

SSC Guru

Points: 116520
More actions
November 18, 2011 at 7:15 am

#250777

Hi SQL Experts.
I need to Calculate First Day of Previous Week.
Today the 1st Day of last Week is Sunday 2011-11-13.
I do not want the time part of the date (preferably).
I'm close to getting what I need but no cigar.:unsure:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 9 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Welsh Corgi (11/18/2011)
Hi SQL Experts.
I need to Calculate First Day of Previous Week.
Today the 1st Day of last Week is Sunday 2011-11-13.
I do not want the time part of the date (preferably).
I'm close to getting what I need but no cigar.:unsure:

What do you want the first day of last week to be on 20th November ? Should it be 20th or 13th?

SELECT CONVERT(DATE,DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6))

--EDIT--

I've converted to "DATE" to completely remove the time, but if you don't mind having 00:00:00.000 then don't bother.

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323547 More actions · Answer 2

for you, when does a week start? on Sunday, right?

this gives you the Monday of "this" week, regardless of the DATEFIRST parameter setting:

select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) As MondayOfThisWeek,

DATENAME(dw,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) As WhichDay

so from there, if you want the Previous sunday, you could dateadd minus 8 days, right?

select dateadd(dd,-8,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) )

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Lowell (11/18/2011)
for you, when does a week start? on Sunday, right?
this gives you the Monday of "this" week, regardless of the DATEFIRST parameter setting:
select DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) As MondayOfThisWeek,
DATENAME(dw,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)) As WhichDay
so from there, if you want the Previous sunday, you could dateadd minus 8 days, right?
select dateadd(dd,-8,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) )

Thanks for the code.:cool:

select dateadd(dd,-8,DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) )

Returns:

(No column name)

2011-11-06 00:00:00.000

I was looking for 2011-11-13.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

Cadavre (11/18/2011)
Welsh Corgi (11/18/2011)
Hi SQL Experts.
I need to Calculate First Day of Previous Week.
Today the 1st Day of last Week is Sunday 2011-11-13.
I do not want the time part of the date (preferably).
I'm close to getting what I need but no cigar.:unsure:
What do you want the first day of last week to be on 20th November ? Should it be 20th or 13th?
SELECT CONVERT(DATE,DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6))
--EDIT--
I've converted to "DATE" to completely remove the time, but if you don't mind having 00:00:00.000 then don't bother.
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)

Returns:

(No column name)

2011-11-13 00:00:00.000

That works but how can it be formated to exclude the time:

2011-11-13

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 5

Welsh Corgi (11/18/2011)
Cadavre (11/18/2011)
Welsh Corgi (11/18/2011)
Hi SQL Experts.
I need to Calculate First Day of Previous Week.
Today the 1st Day of last Week is Sunday 2011-11-13.
I do not want the time part of the date (preferably).
I'm close to getting what I need but no cigar.:unsure:
What do you want the first day of last week to be on 20th November ? Should it be 20th or 13th?
SELECT CONVERT(DATE,DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6))
--EDIT--
I've converted to "DATE" to completely remove the time, but if you don't mind having 00:00:00.000 then don't bother.
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)
Returns:
(No column name)
2011-11-13 00:00:00.000
That works but how can it be formated to exclude the time:
2011-11-13

You quoted my whole post without reading it?!

SELECT CONVERT(DATE,DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6))

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Lowell SSC Guru Points: 323547 More actions · Answer 6

so you want the Sunday that occurred before *today*, that would be the same code, but minus one isntead of minus 8, sorry i misunderstood the question.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Lowell (11/18/2011)
so you want the Sunday that occurred before *today*, that would be the same code, but minus one isntead of minus 8, sorry i misunderstood the question.

It just changed, now I need it to be a day later, Monday so I can say < '2011-11-14'

I works set to 0.

Now I need to do the same in Reporting Services.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Cadavre SSC-Forever Points: 41690 More actions · Answer 8

Welsh Corgi (11/18/2011)
Lowell (11/18/2011)
so you want the Sunday that occurred before *today*, that would be the same code, but minus one isntead of minus 8, sorry i misunderstood the question.
It just changed, now I need it to be a day later, Monday so I can say < '2011-11-14'
I works set to 0.
Now I need to do the same in Reporting Services.

SELECT 'Monday',

CONVERT(DATE,DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 7)), --no time

LEFT(CONVERT(VARCHAR(23),DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 7),21),10), --no time

DATEADD(wk, DATEDIFF(wk, 7, GETDATE()), 7) --00:00:00.000

UNION ALL

SELECT 'Sunday',

CONVERT(DATE,DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6)), --no time

LEFT(CONVERT(VARCHAR(23),DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6),21),10), --no time

DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) --00:00:00.000

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/