All depends on the hotel. For special occasions (birthday/anniversaries/honeymoon) I usually stay at Virtuoso/Amex FHR properties where the agent often proactively asks if there is any special occasion. Stayed in suites at The Peninsula HK and Mandarin Oriental Macau, both Virtuoso stays, on our honeymoon last year and got beautiful cakes and cards signed by the staff. At an Amex FHR property in Bali that I booked online I made no mention of it. I'm of the kind where if I'm asked I'll mentioned it but I'm not going to offer the information up.
On our anniversary last year I burned a free night certificate at a Hilton Curio property in NYC, it was valid for a standard room only and had to be booked by phone and the agent asked if we were celebrating anything. I mentioned the "A" word to the phone agent and were allowed to check-in early (was only expecting to leave our bags), upgraded to a suite, decent F&B credit and late checkout. Not sure how much of that was because of HH Diamond or the anniversary but the way the agent at check-in presented it seemed more occasion based than status based....either way, very nice result.