Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

# get max date record entered in previous month

Posted on 2006-11-16
Medium Priority
334 Views
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?

0
Question by:modish
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 1200 total points
ID: 17955211
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

LVL 75

Assisted Solution

Aneesh Retnakaran earned 800 total points
ID: 17955218
select top 1 *
from urTable  WHERE urDateColumn < CAST(CONVERT(Varchar(6), GETDATE(),112)+'01' as Datetime)
ORDER BY urDateColumn DESC
0

Author Comment

ID: 17964841
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

LVL 143

Expert Comment

ID: 17964863
yes. the dateadd(month, -1) ... will take care of any 31/30/29/28 days per month.
0

Author Comment

ID: 17964885
Excellent and aneeshattingal solution worked as well :-)
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
###### Suggested Courses
Course of the Month11 days, 8 hours left to enroll