I am trying to get the latest date from previous month and when I run this query:
select max(me_date) from table1 having month(me_date) = Month(max(me_date)) - 1
This gives me the latest date for the current month instead of previous. Any ideas?
I am trying to get the latest date from previous month and when I run this query:
select max(me_date) from table1 having month(me_date) = Month(max(me_date)) - 1
This gives me the latest date for the current month instead of previous. Any ideas?
4条答案
按热度按时间cnh2zyt31#
Your code would fail in almost any database, because of the use of non-aggregated functions in the
having
clause.In general, if you want to filter data, you should do so before aggregation. In SQL Server, I would suggest:
The above is index and optimizer friendly. If you don't care about that, then a simpler formulation is:
szqfcxe22#
SELECT EOMONTH(GETDATE()-1) AS 'Last Month';
Click here for more info.
mccptt673#
We can't use having without group by clause, hence the error.
I tested The following query; it works fine in MySQL
Select distinct me_date from <table_name> where month(me_date) not in (select max(month(me_date)) from <table_name> ) order by me_date describe limit 1;
Alternatives of month() function would be datepart(month, me_date) Or, Extract (month from me_date) Or, Strftime('%m', me_date)
Regards!
f0brbegy4#
If you want to get the max(me_date) from previous month of max(me_date) of table1 then try this: