current week range - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

current week range

Author

Topic

Leo_Don
Starting Member

42 Posts

Posted - 2011-02-03 : 06:34:51

Hi,

I am writing a query where the o/p should be from the current week, and want to conform if the below query is giving me the o/p from the current week...
seöect * from table where
createddate between DATEADD(week,datediff(week,0,getdate())-1,0)-1 and GETDATE()

so i should get an o/P of current week i.e for 31-01-11 to 06-02-11 t

thanks in advance....

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2011-02-03 : 07:29:05

Have you tried it? I think this is more on the lines of what you need:

SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0), DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) + 7

- Lumbago
My blog-> www.thefirstsql.com

Ranjit.ileni
Posting Yak Master

183 Posts

Posted - 2011-02-03 : 07:35:36

Hi,
This may useful

set DATEFIRST 1 --Monday
SELECT CONVERT(VARCHAR(8), DATEADD(dd, -(DATEPART(dw, GETDATE())-1), GETDATE()), 5)[WeekStart]
result: 31-01-11
SELECT CONVERT(VARCHAR(8), DATEADD(dd, 7-(DATEPART(dw, GETDATE())), GETDATE()), 5)[WeekEnd]
result:06-02-11

--Ranjit

Leo_Don
Starting Member

42 Posts

Posted - 2011-02-03 : 08:01:57

I need to generate a report for the current week.....
and i think i cant use this in select clause, but can use in the where clause....

below is the full query

Select userid, action, count (action) as 'sum' from table
where
createddate between DATEADD(week,datediff(week,0,getdate())-1,0)-1 and GETDATE()
group by userid, action

action is varchar
so i want all the action in this week, like no matter if i am in the middle of the week
but want the report of this week.
thanks for your help....
the above query work... it gives me result but not sure if that is correct
where

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-03 : 08:41:34

All of the above suggestions will fail when using a different SET DATEFIRST setting.
Try this

SELECT * FROM dbo.Table1
WHERE CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, 0, getdate()) / 7 * 7, 0)
AND CreatedDate < DATEADD(DAY, DATEDIFF(DAY, 0, getdate()) / 7 * 7, 7)

N 56°04'39.26"
E 12°55'05.63"

Leo_Don
Starting Member

42 Posts

Posted - 2011-02-03 : 08:58:04

Hi Peso,

Thank you for the query
can you please explain me the query?

and what if i want the same for the last week, this month and last month?

thanks once again...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-02-03 : 11:05:43

SELECT * FROM dbo.Table1
WHERE CreatedDate >= DATEADD(DAY, DATEDIFF(DAY, 14, getdate()) / 7 * 7, 0)
AND CreatedDate < DATEADD(DAY, DATEDIFF(DAY, 7, getdate()) / 7 * 7, 7)

SELECT * FROM dbo.Table1
WHERE CreatedDate >= DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()), 0)
AND CreatedDate < DATEADD(MONTH, DATEDIFF(MONTH, -1, getdate()), 0)

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources