What's a proper HTTP Status code when the server cannot process the response due to the large number of records that are there in the Database (may be due to the large record size and it's going to taking too much of time to process it) as part of the request that user sent? We wanted to tell the client that there were too many records. The request is semantically correct in this case, but since the server is not able to process it due to it's processing power limitation, we have to give an error response.
For example, let's say client tried to generate all the transactions in the past 3 years out of the maximum allowed duration of 5 years. But since there are more transactions done by the client within 3 years, the server would like to communicate to the client to reduce the duration further. The reason being the hardware resources at the server side is limited and cannot process this huge number of records.
It should not be categorised under the 4xx errors since it's not an issue with the client right? If yes, what's the best 5xx STATUS code to be set for this?
First let's decide on whether it should be 4xx or 5xx code. While it's true that the original problem is not on client side, it's the client who should change the request to make it successful. So it needs to be 4xx.
Sure, if it was just a server bug that you didn't expect - it had to be 500 Internal Server Error. But you decide to live with this defect, so now it's a part of your contract. And client needs to know that if such error happens - a narrower period needs to be requested.
In your case since syntax is correct, 422 Unprocessable Entity is probably the best option. It's still a bit off since 422 should be used when client sends an entity that doesn't make sense logically, but it's the closest code that exists there. You can always include more details in response body.
NB: It's a WebDAV extended code, not the original HTTP code. If you want to go with pure HTTP, I'd use 400 and put the reason in the body so that client could parse it out. I don't think there's anything appropriate in pure HTTP.