Problem with DateTime (6 months of activity)

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 2005 Forums

Transact-SQL (2005)

Problem with DateTime (6 months of activity)

Author

Topic

imanoobatsql
Starting Member

2 Posts

Posted - 2009-06-14 : 23:21:39

Hi,

I'm having problems getting the result of past 6 months of activities in my query.

Here is my code:

SELECT *
FROM [XXXX].[dbo].[vwXXXXX]

WHERE DATEPART(MONTH,[OpenDate])
BETWEEN DATEPART(MONTH,DATEADD(Month,-@month,getDate()))
AND
DATEPART(MONTH,DATEADD(Month,-1,getDate()))

This works fine only if there's no turn of the year involved. For example May 2009 - Jan 2009. However if I tried to get the results from May 2009 - December 2008, there is where the problem arises.

Please Help.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-14 : 23:30:20

[code]
WHERE [OpenDate] >= dateadd(month, datediff(month, 0, getdate()) - 6, 0) -- 6 mth ago
AND [OpenDate] < dateadd(month, datediff(month, 0, getdate()), 0) -- 1st of current month
[/code]

KH
[spoiler]Time is always against us[/spoiler]

imanoobatsql
Starting Member

2 Posts

Posted - 2009-06-15 : 01:14:27

thanks khtan, this is what i need

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources