get max date record entered in previous month

Hello
I have a table where I extract data for a given day - I want to compare this data with data from previous month - but only the data for the last entry for the previous month - how would I do this?

modishAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
select * from yourtable
where yourfield = ( select max ( yourfield ) from yourtable where yourfield <= dateadd(day, 1 - datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120) )   )
0
 
Aneesh RetnakaranConnect With a Mentor Database AdministratorCommented:
select top 1 *
from urTable  WHERE urDateColumn < CAST(CONVERT(Varchar(6), GETDATE(),112)+'01' as Datetime)
ORDER BY urDateColumn DESC
0
 
modishAuthor Commented:
Apologies for the late response
thanks to you both for your responses - I couldn't get aneeshattingals one to work  problem with casting.- but angelIII worked brilliantly -
I changed it to be
 yourfield <= dateadd(day,  - datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120) )  
to get the last day of the previous month ..
Is this the best way to do the same for the month before that.
yourfield <=  dateadd(month,-1,dateadd(day, 1 - datepart(day, getdate()), convert(datetime, convert(varchar(10), getdate(), 120), 120) )   )

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes. the dateadd(month, -1) ... will take care of any 31/30/29/28 days per month.
0
 
modishAuthor Commented:
Excellent and aneeshattingal solution worked as well :-)
0
All Courses

From novice to tech pro — start learning today.