According to Terraria's official wiki page on Water Candles:
Holding a Water Candle in your hand will increase the monster spawn probability to 133% and the maximum number of monsters to 150%.
They produce light like a regular torch or candle would. If you have them on tables as props, they produce some lovely spooky lighting. However, they don't increase the spawn rate like this.
To increase the spawn rate with a water candle, hold it in your hand. It can't just be in the top row of your inventory - it must be the currently active and held item.
Keep a weapon handy so that you can swap back and forth between your water candle and weapon as necessary. You can't hold both at the same time, but you can at least run around luring enemies out with your water candle, attack them all, then return to luring enemies out.
Answer from doppelgreener on Stack Exchangeterraria - what is the right way to use a water candle? - Arqade
thermodynamics - How to use candle heat? - Physics Stack Exchange
Do water candle and shadow candle effects stack?
Water Candles have no effect?
Videos
According to Terraria's official wiki page on Water Candles:
Holding a Water Candle in your hand will increase the monster spawn probability to 133% and the maximum number of monsters to 150%.
They produce light like a regular torch or candle would. If you have them on tables as props, they produce some lovely spooky lighting. However, they don't increase the spawn rate like this.
To increase the spawn rate with a water candle, hold it in your hand. It can't just be in the top row of your inventory - it must be the currently active and held item.
Keep a weapon handy so that you can swap back and forth between your water candle and weapon as necessary. You can't hold both at the same time, but you can at least run around luring enemies out with your water candle, attack them all, then return to luring enemies out.
Water candles placed in the world and those actively held in your hand increase the spawn rate and maximum number of enemies. As of 1.3.0.1 these effects do not stack, and a maximum of one candle will count at a time.
For versions before 1.3 see the old answer below.
According to the disassembled code (1.2.0.3.1), placed water candles also affect spawnrate and max spawns. This effect is separate from holding the candle in your hand and will stack with it. A single placed water candle is enough and only one matters.
NPC.spawnRate = (int)((double)NPC.spawnRate * 0.75); // Spawns happen 33% more often, spawn interval multiplied by 0.75
NPC.maxSpawns = (int)((float)NPC.maxSpawns * 1.5f); // Spawn limit increased by 50%, multiplied by 1.5
Both effects are the same and when stacked (holding a candle and placed a candle) spawnrate is increased by 78% and maximum spawned enemies is increased by 125%.
Temperature is not the same as heat. A candle flame has a very high temperature, but the substance which is actually hot is a thin gas. As a result, the total amount of heat being produced by the candle is quite small - a quick google search suggests that a typical candle might generate somewhere on the order of 
to 
joules of heat per second.
Even a small space heater would typically generate 10 to 20 times that amount. So the answer to your question is that it isn't impossible, you'd just need quite a few candles.
As another answer says, heat is different from temperature. Think about the bright blue arcs you get between your finger and the doorknob when you get carpet-shocked in the winter. Those sparks are blue because they are hotter than the surface of the sun. But the total energy involved in a carpet shock is a few millijoules. It’s the energy you care about, not the temperature.
Another answer also claims that the thermal power of a candle flame is 50–100 watts. That’s comparable to an old incandescent light bulb. Like a candle flame, those bulbs were too hot to hold while they operated. But you had to really pay attention to decide if a single light bulb was really heating up a room.
A kilowatt (ten hundred-watt candles) is the usual power of a small electric stove burner. (The big burners are usually 2500 watts.) You don’t ordinarily think of boiling a teakettle as an activity which warms up your house. But imagine boiling a teakettle, then pouring the hot water into a hot water bottle, and boiling another kettle, to fill another hot water bottle, all day. Eventually, your first hot water bottle would be cool enough that you could put that cooled water back in the teakettle instead of getting fresh water from the tap. That’s an idea of how much heat you could really extract from some candles.
A suggestion I have encountered for winter blackouts (which you mention in a comment) is to get some empty terra-cotta flowerpots from your garden shed, and put them upside down over a candle. Leave a gap underneath for fresh air to come in, and the hot exhaust can escape via the drain hole in the planter. I have tried this. There is a sweet spot between the little flowerpots which get too hot to hold, versus the big ones that you can’t really tell whether they have gotten warmer or not. I certainly didn’t feel like having a hot flowerpot upside down over a candle on my dining table made my room noticeably warmer. Water is probably a better material for storing heat than terra cotta anyway.