last week records without hardcoding dates

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

SQL Server 2000 Forums

SQL Server Development (2000)

last week records without hardcoding dates

Author

Topic

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-07-14 : 05:07:46

Hi,

I need records of last week (Monday to sunday) without harcoding dates. Run on any day and records should be displayed of previous week.

create table #temp1
(
product varchar(200),
importdate datetime
)

insert into #temp1 values ('abc','2010-07-03 22:20:12.457')
insert into #temp1 values ('efg','2010-07-05 22:20:12.660')
insert into #temp1 values ('hki','2010-07-06 11:32:49.147')
insert into #temp1 values ('lmn','2010-07-07 15:12:38.160')
insert into #temp1 values ('opq','2010-07-08 11:02:34.290')
insert into #temp1 values ('rst','2010-07-09 09:53:59.553')
insert into #temp1 values ('uvw','2010-07-14 09:53:59.553')

select * from #temp1
where importdate >= '2010-07-05' and importdate <= '2010-07-11'

Regards,
Sachin

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-14 : 05:19:16

SELECT * FROM #Temp1
WHERE ImportDate >= DATEDIFF(DAY, 1, GETDATE()) / 7 * 7
AND ImportDate < DATEDIFF(DAY, 0, GETDATE()) / 7 * 7

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

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-07-14 : 06:11:29

no result from the above query

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-14 : 07:05:28

Workd for me

DECLARE	@Sample TABLE
	(
		Product VARCHAR(200) NOT NULL,
		ImportDate DATETIME NOT NULL
	)

INSERT	@Sample
SELECT	'abc', '20100703 22:20:12.457' UNION ALL
SELECT	'efg', '20100705 22:20:12.660' UNION ALL
SELECT	'hki', '20100706 11:32:49.147' UNION ALL
SELECT	'lmn', '20100707 15:12:38.160' UNION ALL
SELECT	'opq', '20100708 11:02:34.290' UNION ALL
SELECT	'rst', '20100709 09:53:59.553' UNION ALL
SELECT	'uvw', '20100714 09:53:59.553'

SELECT	*
FROM	@Sample

SELECT	*
FROM	@Sample
WHERE	ImportDate >= DATEDIFF(DAY, 7, GETDATE()) / 7 * 7
	AND ImportDate < DATEDIFF(DAY, 0, GETDATE()) / 7 * 7

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

sachingovekar
Posting Yak Master

101 Posts

Posted - 2010-07-14 : 08:02:53

thanks peso. your previous query had 1 instead of 7 thats why it was not working.

many thanks again
sachin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources