report for last month - 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)

report for last month

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2006-03-08 : 11:17:42

Hi all

how can i get the report to run between start date of last month to last day of last month

for example

report run today but it will run for date between Feb 1 to Feb 28?

thanks

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2006-03-08 : 11:23:57

DECLARE @Today DATETIME
DECLARE @FirstOfMonth DATETIME
DECLARE @LastOfMonth DATETIME

SET @Today = CURRENT_TIMESTAMP
SET @FirstOfMonth = DATEADD(month, DATEDIFF(month, 0, @Today)-1, 0)
SET @LastOfMonth = DATEADD(day, -1, DATEADD(month, 1, @FirstOfMonth))

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-08 : 11:27:28

This will do it:


select
	*
from
	MyTable
where
	-- Greater than or equal to start of last month
	MyDateCol >= dateadd(mm,datediff(mm,0,getdate())-1,0) and
	-- Less than the start of current month
	MyDateCol <  dateadd(mm,datediff(mm,0,getdate()),0)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources