DATEPART - 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

SQL Server 2000 Forums

SQL Server Development (2000)

DATEPART

Author

Topic

igorblackbelt
Constraint Violating Yak Guru

407 Posts

Posted - 2004-11-05 : 16:18:53

Hello Guys
I need to pull data from this table for last month only by "ReceivedDate", I have a criteria set for date, but I'm not getting the results I think I should, can anybody review my code?

Thanks!

WHERE (DATEPART(dw, GETDATE()) = 2) AND (DATEPART(m, dbo.DateReceived) >= DATEPART(m, GETDATE()) - 1) AND (DATEPART(d, GETDATE())
= 1 OR
DATEPART(d, GETDATE()) = 2 OR
DATEPART(d, GETDATE()) = 3) OR
(DATEPART(m, dbo.DateReceived) >= DATEPART(m, GETDATE()))

I Love Challenges! That's why I love coding!

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-05 : 16:56:35

So.... reformating and removing some of the extra parens I get this:


WHERE 
	DATEPART(dw, GETDATE()) = 2   --is it monday
	AND 
	DATEPART(m, dbo.DateReceived) >= DATEPART(m, GETDATE()) - 1  --is it a later or equal to last month
	AND 
	(
		DATEPART(d, GETDATE()) = 1   -- is it the 1st day of the month
		OR
		DATEPART(d, GETDATE()) = 2   -- is it the 1st day of the month
		OR
		DATEPART(d, GETDATE()) = 3   -- is it the 1st day of the month
	) 
	OR
	DATEPART(m, dbo.DateReceived) >= DATEPART(m, GETDATE())  -- is it in the future

I have to be honest this looks kind of silly. The last line says anything the future is cool [:|]

If all you are trying to determine is did it happen last month then the following should be closer:


--display your range
Select 
	convert(datetime,convert(varchar,dateadd(m,-1,dateadd(dd,1-day(getdate()),getdate())),101)),
	convert(datetime,convert(varchar,dateadd(dd,-day(getdate()),getdate()),101))


--implemented
Where 
	dbo.dateReceived between convert(datetime,convert(varchar,dateadd(m,-1,dateadd(dd,1-day(getdate()),getdate())),101))
		and convert(datetime,convert(varchar,dateadd(dd,-day(getdate()),getdate()),101))

Corey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources