The Q9400 processor is old, but by no means is it weak. Core 2 Quad's are still viable processors, and should get you through daily tasks with ease. Your CPU is better in performance than my current Sandy Bridge Pentium G840.
If you're looking to play the latest games however, you may meet the minimum requirements CPU wise, depending on the game. To play modern and upcoming titles at high graphical settings, it'll be dependant on your GPU, however tied in with an i5 would be ideal, or at least an i3 if you're on a budget.
You don't necessarily need to upgrade your CPU. Gaming wise, you should look at your GPU; if you already have a decent/high-end GPU, then I suppose that upgrading your CPU is your next bet. If you could reply back with your current computer specifications, and if you desire to play games, could you list the games you're looking to play? From there onwards I will advise if a CPU/GPU upgrade is necessary.
If you are however looking to upgrade your processor, could you please reply back with your budget? To upgrade your CPU you'll need a new motherboard. Do you currently have DDR2 or DDR3 RAM? Finally does your current PSU have SATA connectors, and are your HDD/SSD/ODD SATA? With those questions answered, I'll provide a cheap upgrade for you. If your system does not have SATA connectors, then building a new system all together may be ideal for you.
To simplify your original question's answer: Your processor still has a lot of life in it. If you're satisfied with your current performance, then there is no actual need to upgrade. If you're looking to play more modern graphically intensive games, that requires a GPU, and is not solely dependant on your CPU. If you already have a GPU, you could upgrade your CPU/motherboard/RAM and may be even your GPU too for better gaming performance.
If you don't already have DDR3 RAM, then to upgrade your CPU you will need to purchase DDR3 RAM, a motherboard, and of course a new CPU.
All the best.