It says that he can give any character the Night's Watch trait, but it doesn't specify any character in play. Would it be legal for me to use this ability on a character in my hand in order to benefit from an effect like The Wall?

No. Unless a card says otherwise, it can only ever affect other cards in play.

AsaTJ said:

The default in this game is that unless a card specifically says you can, you can't - unlike other games that take the opposite approach (ie, unless it says you can't, you can).