Select last date of month in table 3

[gaperry]

I need to select the last date for any given month that exists in a table on sql server. I have looked at books online and other help topics but can't find how to do this.

Any help would be appreciated.

Thanks

 

[fluteplr]

select max(mydate) from mytable where
mydate between '2001-01-01' and '2001-01-31'

replace the dates with the dates of your choice.

 

[gaperry]

Oops. I forgot the mention that I am actually trying to select another field in the table based on the MAX date for any given month in the table. I don't know how much this changes your solution. Thanks

 

[tlbroadbent]

Building on Fluteplr's solution, you can do the following using a sub-query.
[tt]
Select * From MyTable
Where MyDate =
(select max(mydate)
from mytable
where mydate
between '2001-01-01'
and '2001-01-31')[/tt] Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[gaperry]

Thank you both very much. I am trying to get most of my coding done before Monday.

Thanks again

 

[gaperry]

Based on Terry's suggestion that was built on fluteplr's original suggestion, How would I do the below statement programmatically if the table contains several months worth of data ? What I am doing is pulling a currency exchange rate from one field based on the highest date for a given month in the table. I am trying to code this so that I don't have to hardcode dates in as shown below. (In other words, I won't always know the start and end dates unless I look them up in the table)


Select * From MyTable
Where MyDate =
(select max(mydate)
from mytable
where mydate
between '2001-01-01'
and '2001-01-31')



Thanks, George

 

[moonshadow]

Not too sure what you want (are you wanting the exchange rate for each month, for the latest date in the month). If so, you could try,
select MyDate, ExchangeRate from MyTable
where MyDate in (select max(MyDate) from MyTable group by Year(MyDate), Month(MyDate))
Personally, I hate these subselects, and would rather create a view based on the subquery, and join the view and table back together, but as there's only one field to join on, I guess it's OK. The subselect is producing a list of dates, which are the latest dates for each month in the table (If you always have less than one years data, you can dispense with the Year(MyDate) bit). Hope this helps

 

[gaperry]

moonshadow, thanks. Exactly what I was looking for.