GETDATE or MONTH Functions - 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

Transact-SQL (2000)

GETDATE or MONTH Functions

Author

Topic

kwalker
Starting Member

4 Posts

Posted - 2005-01-27 : 15:08:19

I'm new to T-SQL (Oracle for last four years), so I'm having difficulty getting the syntax correct on returning a count of records that fall within a given month. The current statement reads:

select count(*)
from [tablename]
reportdate >= dateadd (m, -2,getdate()) and
reportdate <= dateadd (m, -1,getdate())

But this uses the GETDATE function and subtracts one month and two months, respectively, to create a range of values. I wish to modify this script to get the current month, last month, two months ago, etc.

For example, if the GETDATE function returns 01/27/05, the old statement as defined with the parameters above returns a floating recordset (see below):

12/27/04 - 01/27/05 - Current Month
11/27/04 - 12/27/04 - Current Month -1
10/27/04 - 11/27/04 - Current Month -2

For example, if the GETDATE function returns 01/27/05, the new statement as defined with the parameters above should returns a static recordset (see below):

01/1/05 - 01/27/05 - Current Month
12/1/04 - 12/31/04 - Current Month -1
11/1/04 - 11/30/04 - Current Month -2

Thanks, in advance, for your assistance.

X002548
Not Just a Number

15586 Posts

Posted - 2005-01-27 : 16:03:35

ISn't this what you want?

WHERE yourDate > DATEADD(m,-3,GetDate())

Brett

8-)

kwalker
Starting Member

4 Posts

Posted - 2005-01-27 : 16:32:53

I'm sorry, but this statement yields a set of records in January, December and November. I wish to only include, say for instance, all the records in the month of December.

I appreciate your willingness to help.

TimS
Posting Yak Master

198 Posts

Posted - 2005-01-27 : 16:51:34

Do you wish to use WHERE DATEPART ( mm , reportdate ) = 12 -- for December

Do you wish to use WHERE DATEPART ( mm , reportdate ) = DATEPART ( mm , GETDATE() ) -- for current month

Edit:
You can also use the MONTH function; MONTH ( date )

Tim S

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-01-27 : 17:08:44

I have found this type of query helpfull for reporting counts by month. Its not the fastest query in the world to run depending on the size of your table but its effective


SElect	DatePart(year, DateCol)		[Year]
	,DatePart(month, DateCol)	[Month]
	,count(*)			[RecCount]
From	TableWithDateCol
Where	DateCol Between '4/1/2003' AND '4/30/2004'
Group by DatePart(year, DateCol)
	,DatePart(month, DateCol)
Order by DatePart(year, DateCol) desc
	,DatePart(month, DateCol)

kwalker
Starting Member

4 Posts

Posted - 2005-01-27 : 17:09:09

Excellent response, but I failed to include a requirement. The statements you've provide yield results in all past years, not just in the last twelve months. So, if I could get some help limiting it to records in December, but only the December within one year of the GETDATE. I'm almost there ...

Thanks!

TimS
Posting Yak Master

198 Posts

Posted - 2005-01-27 : 17:42:50

Look up the YEAR or DATEPART in BOL Books on Line and it is easy to add the year requirement.

Edit: BOL is at http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

Tim S

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-01-27 : 17:48:17

I am interested in actual T-SQL query for this?

TimS
Posting Yak Master

198 Posts

Posted - 2005-01-27 : 17:52:59

WHERE DATEPART ( mm , reportdate ) = DATEPART ( mm , GETDATE() ) AND
DATEPART ( yy , reportdate ) = DATEPART ( yy , GETDATE() )

Tim S

TimS
Posting Yak Master

198 Posts

Posted - 2005-01-27 : 18:18:41

NOTE: replace update_utc with reportdate for last 3 months Nov, Dec, Jan right now.

WHERE DATEPART ( yy , update_utc ) * 100 + DATEPART ( mm , update_utc ) BETWEEN
DATEPART ( yy , DATEADD ( mm , -2, GETDATE() ) ) * 100 + DATEPART ( mm , DATEADD ( mm , -2, GETDATE() )) AND
DATEPART ( yy , GETDATE() ) * 100 + DATEPART ( mm , GETDATE())

polygonSQL
Starting Member

8 Posts

Posted - 2005-01-28 : 06:33:01

try this--

select count(*)
from [tablename]

month(reportdate) = month(getdate)) and
year(reportdate) = year(getdate))

reportdate >= dateadd (m, -2,getdate()) and
reportdate <= dateadd (m, -1,getdate())

select * from end_user
where clue > 0
GO
( 0 rows returned )

kwalker
Starting Member

4 Posts

Posted - 2005-01-28 : 12:22:10

Thanks to everyone that gave me guidance in arriving at a solution. For the benefit of others, I have arrived at the following code to meet my needs:

select * from [tablename]
where DATEDIFF(month, reportdate, getdate()) < 1

This yields the records in the current month.

select * from [tablename]
where DATEDIFF(month, reportdate, getdate()) = 1

This yields the records in last month.

select * from [tablename]
where DATEDIFF(month, reportdate, getdate()) = 2

This yields the records in the month two months ago.

Etc.......

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources