find same week in previous year

schuitkds
schuitkds used Ask the Experts™
on
Hello
I would I construct critera in a query to look for data  the same week as current week.
Example:  
current is 27 , I would like to find data for the same week last year
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Use the following query

SELECT * FROM table_name WHERE
DATEPART (wk,date_field) = DATEPART (wk, DATEADD(year,-1,getDate())) and
year(date_field) = year(getDate()) - 1

Author

Commented:
how does this give me the same week number last year as the current week #

Author

Commented:
example
current week # is 27
how do I get the date for week # 27 last year?
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Top Expert 2010

Commented:
This will give you the current week number:

DATEPART (wk, getDate())

This will compare last year week

SELECT * FROM table_name WHERE
DATEPART (wk,date_field) = DATEPART (wk, getDate()) and
year(date_field) = year(getDate()) - 1
try this:

SELECT DATEPART (wk, DATEADD(wk,DATEPART (wk, GETDATE()) - 1,CONVERT(DATETIME,'01/01/' + CONVERT(VARCHAR(4),DATEPART(yyyy,GETDATE()) - 1))))
Description:
Find last year first datetime and Add this year weeks elapsed to that

Commented:
In SQL Server, start of second week # always very depending on day
Check out below little sql block. It will give you last year's same week #'s first day and last day.
Declare @PrevYearWeekFirstDate datetime
Declare @PrevYearWeekLastDate datetime
SET @PrevYearWeekFirstDate = dateadd(d, 1 + datepart(wk, getdate()) * 7 - datepart(dw, '1/1/' + cast(datepart(yyyy, getdate()) - 1 as varchar) )   , '1/1/' + cast(datepart(yyyy, getdate()) - 1 as varchar))
SET @PrevYearWeekLastDate = DATEADD(d, 6, @PrevYearWeekFirstDate)
Print @PrevYearWeekFirstDate
Print @PrevYearWeekLastDate
Hi,


Try it using below code :

SELECT DATEPART(wk,getdate())
SELECT DATEPART (wk, DateAdd(Year,-1,getDate()))

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial