Last Month Query

[Schnappa]

All

I know this one has probably been discussed to death before now, however if only once more...

I am trying to get this coding right, so it will enable me to retrieve all transactions of the previous month. It is the combination of DatePart and DateAdd that has me confused. The following is what I have (this is quizzing a Sybase DB)

WHERE (DatePart(month,dp_history.effective_dt) = DATEPART(mm, GETDATE())) AND (DatePart(year,dp_history.effective_dt) = DATEPART(yy, GETDATE()))

Secondly, I wanted to be able to adjust the code to tak into account if the current month is January. If this was the case, I would then need to not only look for month -1, but also year -1. This one is a nice to have, but I thought I might as well ask at the same time.

Cheers

Gezza

 

[Jamfool]

quick stab:

Create table #somedates
(
id int,
adate datetime
)
insert into #somedates
select 1,'2004-01-01 00:00:00.000'
UNION ALL
select 2,'2005-01-01 00:00:00.000'
UNION ALL
select 3,'2005-12-01 00:00:00.000'
UNION ALL
select 4,'2006-01-01 00:00:00.000'
UNION ALL
select 5,'2006-02-01 00:00:00.000'
UNION ALL
select 6,'2006-07-07 00:00:00.000'
UNION ALL
select 7,'2006-08-07 00:00:00.000'

go
declare @month int,@year int
declare @mysearchdate datetime
select @mysearchdate = dateadd(m,-8,getdate())

set @month = datepart(month,@mysearchdate)
set @year = datepart(year,@mysearchdate)

select * from #somedates
where 
(DatePart(month,#somedates.adate) = @month or @month is null)
and
(DatePart(year,#somedates.adate) = @year or @year is null)

drop table #somedates

 

[danvlas]

GetDate()-Day(GetDate()) will return the last day of previous month.

Where (Year(dp_history.effective_dt) = Year(GetDate()-Day(GetDate()))) and (Month(dp_history.effective_dt) = Month(GetDate()-Day(GetDate())))

would be a good filter for any previous month...

HTH



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html