Current week, last week, current month and last month

Question

Post reply

Current week, last week, current month and last month

Corobori

Ten Centuries

Points: 1316
More actions
December 3, 2005 at 11:30 am

#92671

How can I select records on a full date (datetime) when I need to select all records for the Current week, last week, current month and last month ?

Jean-Luc
www.corobori.com

Viewing 11 posts - 1 through 10 (of 10 total)

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

Charles Otten Default port Points: 1475 More actions · Answer 1

Use the Datediff function:

Current Week: DateDiff(wk,datefield,getdate()) = 0

Previous Week: DateDiff(wk,datefield,getdate()) = 1

Current Month: DateDiff(mm,datefield,getdate()) = 0

Previous Month: DateDiff(mm,datefield,getdate()) = 1

If the phone doesn't ring...It's me.

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 2

Charles,

Very nice. And if you reverse the dates, the numbers make even more sense! I added a couple more to show the trend...

Previous Week: DateDiff(wk,getdate(),datefield) = -1
Current Week: DateDiff(wk,getdate(),datefield) = 0
Next Week: DateDiff(wk,getdate(),datefield) = 1

Previous Month: DateDiff(mm,getdate(),datefield) = -1
Current Month: DateDiff(mm,getdate(),datefield) = 0
Next Month: DateDiff(mm,getdate(),datefield) = 1

The only problem is that it will never do any better than an INDEX SCAN... do the following comparison with the "Execution Plan" turned on (run both at the same time)....

USE NORTHWIND

GO

SELECT SHIPPEDDATE

FROM Orders

WHERE DATEDIFF(wk,'07/22/1996',ShippedDate) = -1

SELECT ShippedDate

FROM Orders

WHERE ShippedDate>= DATEADD(wk,DATEDIFF(wk,0,'07/22/1996')-1,0)

AND ShippedDate< DATEADD(wk,DATEDIFF(wk,0,'07/22/1996'),0)

Basically, the second query should run 6.26 times faster when an index is present on the "datefield" or SHIPPEDDATE column... thats 626% times faster (if you like big numbers).

The good news is that if an index is not present on the column, they perform identically...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sergiy SSC Guru Points: 110208 More actions · Answer 3

Consider to take only last script from Jeff's post as an example.

All other ways will eliminate index on DateField and badly affect performance.

And "if an index is not present on the column" it must be created.

Moreover, if you use range select from the column this index MUST BE CLUSTERED.

_____________
Code for TallyGenerator

Corobori Ten Centuries Points: 1316 More actions · Answer 4

Thanks,

Just one more thing: the SQL Server I am working with is US based, where the week starts on Sunday whereas I need to have the week starts on Monday. Anything I can do ?

Jean-Luc
www.corobori.com

Sergiy SSC Guru Points: 110208 More actions · Answer 5

DateVal - ((DATEPART(dw, DateVal) + @@DATEFIRST + 5) %7)

gives you last monday before DateVal. If DateVal is Monday it will return DateVal.

_____________
Code for TallyGenerator

Charles Otten Default port Points: 1475 More actions · Answer 6

An easier way is to set the @DateFirst to show day 1 as Monday. Do this with:

set datefirst 1

If the phone doesn't ring...It's me.

84gopi Mr or Mrs. 500 Points: 567 More actions · Answer 7

Hi,

Can you help me in getting query to display current week number, previous 9 weeks & next 6 weeks for a year......

I have report requirent to display like current week number as 0 & previous 9 week no's as -9,-8,-7......like below

20124041424344454647484950

51 525312

Oct-12Nov-2012 Dec-2012Jan-2013

-9-8-7-6-5-4-3-2-10123456

Thanks

Gopi

Jeff Moden SSC Guru Points: 1004309 More actions · Answer 8

84gopi (7/24/2012)
Hi,
Can you help me in getting query to display current week number, previous 9 weeks & next 6 weeks for a year......
I have report requirent to display like current week number as 0 & previous 9 week no's as -9,-8,-7......like below
20124041424344454647484950
51 525312
Oct-12Nov-2012 Dec-2012Jan-2013
-9-8-7-6-5-4-3-2-10123456
Thanks
Gopi

There are 7 days in a week. Which one of them is the first day of the week for you?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ant-Green SSC Guru Points: 113499 More actions · Answer 9

84gopi (7/24/2012)
Hi,
Can you help me in getting query to display current week number, previous 9 weeks & next 6 weeks for a year......
I have report requirent to display like current week number as 0 & previous 9 week no's as -9,-8,-7......like below
20124041424344454647484950
51 525312
Oct-12Nov-2012 Dec-2012Jan-2013
-9-8-7-6-5-4-3-2-10123456
Thanks
Gopi

Again dont hijack other peoples threads, you already have a thread for this question here

84gopi Mr or Mrs. 500 Points: 567 More actions · Answer 10

Hi,

If you consider zero as current week in then i should get previous 0 weeks like -9,-8,-7,-6 and the next 6 weeks from 0 like below .......

2012404142434445464748495051525312

Oct-12Nov-2012Dec-2012Jan-2013

-9-8-7-6-5-4-3-2-10123456

Please help me to with the function or expression ..........which displays above.........

Thanks

Gopi