What is the difference between profile and content profile module? There is a core module called Profile which adds the profile fields; there is also a contributed module called Content Profile that does the same thing (up to my knowledge.)

May anyone clear me the difference between these two modules?
In which case should we go for the Profile module, and in which case for Content Profile module?
Assuming you're in Drupal 6, Content Profile allows you to set up a node type that will act as user profiles.  this is more extensible than the core profile module, which essentially just adds basic fields.

Also, because Content Profile profiles are nodes, you get more flexibility with what fields you can have on a user profile.  Any core or contrib field will be usable with Content Profile ( e.g. image field, file field, node reference), whereas the core profile module has limited options.  You can also use Content Profiles with Views more efficiently.

In short:

Content Profile creates user profiles as nodes, with all the benefits that entails (Views being the big one for me).

Core profile module adds a basic profile to the user that has less customisation/options.

Check out this video for some more help with Content Profile.