Getting results till Sunday date

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

Getting results till Sunday date

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-06 : 13:58:18

Hi All,

The below query shows me allthe orders from Jan 26 to Feb 1 , that is LAst Week (Mon -Sun). What I need is if I run the query today (2/6) the
orders should be from Jan 1 to last Sun (Feb 1), If I run the query on (2/10) the orders should be from Jan 1 to Sun (2/8).

SELECT Order_Received_Date
FROM
orders
WHERE
( ( dbo.v_tbl_orders_base.Order_Received_Date ) > convert(datetime,convert(varchar(11),dateadd(ww,-1,getdate())- datepart(dw,getdate())+2)) and
( dbo.v_tbl_orders_base.Order_Received_Date ) < convert(datetime,convert(varchar(11),getdate()- datepart(dw,getdate())+2)) )

Thanks for your time & help,
Petronas

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-02-06 : 14:44:53

This shows how to get the start of year and the date for last Sunday for any given date.



select
	a.DATE,
	Start_of_year	= dateadd(year,datediff(year,0,a.DATE),0),
	Last_Sunday	= dateadd(dd,(datediff(dd,-53684,a.DATE)/7)*7,-53684)
from
	( -- Test Data
	select DATE = convert(datetime,'20090206')	union all
	select DATE = convert(datetime,'20090210')
	) A



Results:
DATE                    Start_of_year           Last_Sunday
----------------------- ----------------------- -----------------------
2009-02-06 00:00:00.000 2009-01-01 00:00:00.000 2009-02-01 00:00:00.000
2009-02-10 00:00:00.000 2009-01-01 00:00:00.000 2009-02-08 00:00:00.000

(2 row(s) affected)

CODO ERGO SUM

Petronas
Posting Yak Master

134 Posts

Posted - 2009-02-06 : 15:13:46

Hi Michael,

Thanks for the query. It is what I need. Though this query shows data for only the Start_of_Year and the Last_Sunday. What I want is
result set list from Start_of_Year till the Last_Sunday.
Eg: 1/1/2009 (Start of year)
1/2/2009
....
....
2/1/2009 (Last Sunday)

I appreciate your help and time.

Thanks again,
Petronas

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-02-07 : 00:01:17

here is what u want, check this


DECLARE	@Date		DATETIME
DECLARE	@StartDate	DATETIME
DECLARE	@EndDate	DATETIME

SELECT	@Date = '02/06/2009'
SELECT	@StartDate = DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)
SELECT	@EndDate = DATEADD(DD,(DATEDIFF(DD,-53684,@Date)/7)*7,-53684)

-- SELECT @StartDate, @EndDate

SELECT	DATEADD(DAY, number, @StartDate)
FROM		Master..spt_values
WHERE		type = 'P'
		AND DATEADD(DAY, number, @StartDate) <= @EndDate

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-02-07 : 07:54:40

Also this if sql 2005,


DECLARE @date datetime
SET @date='20090206'

;With Date_CTE (DateVal)
AS
(SELECT DATEADD(yy,DATEDIFF(yy,0,@date),0)
UNION ALL
SELECT DATEADD(dd,1,DateVal)
FROM Date_CTE 
WHERE DATEADD(dd,1,DateVal)<=DATEADD(wk,DATEDIFF(wk,0,@date),0)
)
SELECT * FROM Date_CTE

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources