previous month on first day

Newbie question. I am using a query that pulls month-to-date data that has the following where clause:

WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE()))

this works just fine but what I would like for it to do is give me the previous month of data if the if it's

the first day of the month and then any other day give me month to date. Is this possible?

Thanks in advance,

Marco

Could you just use GETDATE()-1?

Code Snippet

WHERE (MONTH(datefield) = MONTH(GETDATE()-1)) AND (YEAR(datefield) = YEAR(GETDATE()-1))

HTH!

|||

I'm sorry, I should have added that I want to accomplish this without having to modify the query every month.

|||

Sorry, i don't understand. Why would you have to modify the query every month?

|||

Well, throughout the entire month I use WHERE (MONTH(datefield) = MONTH(GETDATE())) AND (YEAR(datefield) = YEAR(GETDATE())) to get the month to date data and then when the new month starts Ihave to change it to WHERE (MONTH(datefield) = MONTH(GETDATE()-1)) AND (YEAR(datefield) = YEAR(GETDATE())) to get the previous month and then change it back to continue the month-to-date data.

|||

But as you're only matching on month and year, you won't have to change it. GETDATE()-1 will take one day off the date, and so it just means that on the 1st day of the month, it will use the last day of the last month whereas any other day within that month, the month and year values are the same.

Test it out:

Code Snippet

declare @.date datetime

set @.date = '1 nov 2007'

select month(@.date-1), year(@.date-1)

declare @.date datetime

set @.date = '20 nov 2007'

select month(@.date-1), year(@.date-1)

|||

Code Snippet

SELECT DATEADD(m,-1,DATEADD(m, DATEDIFF(m, 0, '8/29/07'), 0))

Lee Everest

Sr. Consultant - Sogeti USA LLC

www.texas2oo.com/sqlblog

|||

You can combine the 2 WHERE clause like this :

select datefield,month(datefield) from dbo.datefieldtable

WHERE (

MONTH(datefield)=

case when DATEPART(day, GETDATE())=1 then MONTH(GETDATE()-1)

else MONTH(GETDATE()) end )

AND

YEAR(datefield) = YEAR(GETDATE())

--with DATEPART(day, GETDATE())=1 you can obtain the first day of month

You have to see the performance!

|||

here it is,

Code Snippet

where

datefield >= case when day(getdate()) = 1 then

cast(convert(varchar,dateadd(mm,-1,getdate()),101) as datetime)

else

cast(convert(varchar,dateadd(dd,1-day(getdate()),getdate()),101) as datetime)

end

and

datefield < case when day(getdate()) = 1 then

cast(convert(varchar,getdate(),101) as datetime)

else

cast(convert(varchar,getdate()+1,101) as datetime)

end

|||

Perhaps the range can be obtained by something like:

Code Snippet

declare @.testDates table (dt datetime)
insert into @.testDates
select '7/20/7' union all
select '7/31/7' union all
select '8/1/7' union all
select '8/2/7' union all
select '8/5/7' union all
select '8/31/7' union all
select '9/1/7' union all
select '9/2/7'
--select * from @.testDates

select dt,
dateadd(mm, datediff(mm, 0, dt-1), 0) as rangeLow,
dateadd(mm, datediff(mm, 0, dt-1) + 1, 0) as rangeHi,
dateadd(mm, datediff(mm, 0, dt-1) + 1, 0)-1 as monthEnd
from @.testDates

/*
dt rangeLow rangeHi monthEnd
--
2007-07-20 2007-07-01 2007-08-01 2007-07-31
2007-07-31 2007-07-01 2007-08-01 2007-07-31
2007-08-01 2007-07-01 2007-08-01 2007-07-31
2007-08-02 2007-08-01 2007-09-01 2007-08-31
2007-08-05 2007-08-01 2007-09-01 2007-08-31
2007-08-31 2007-08-01 2007-09-01 2007-08-31
2007-09-01 2007-08-01 2007-09-01 2007-08-31
2007-09-02 2007-09-01 2007-10-01 2007-09-30
*/

|||

Got it. Thank you all.