The best time to give up on a dream is when it no longer excites or inspires you.
Many people would suggest giving up on unrealistic dreams, but I disagree with that sentiment. If a dream is unrealistic in the present but it still inspires you then for the time being put it aside and pursue more realistic - if more short term - goals.
The beauty of a dream is that it doesn't have to be realistic or practical. It can be the most ridiculously unreachable of goals - the fantastic nature of a dream is what makes it a dream rather than a specific concrete goal.
Your dream doesn't have to be the center of your universe. Take your dream job, for example. You can pursue a realistic career in the food services industry while you dream of becoming an internationally best selling author. Many people who can write well know that writing does not necessarily pay much, but you can always write on the side while you make your real living by other means. The same holds true for dreams of making a living singing, acting or playing professional sports.
The best part of having a dream that you never give up on is that it can inspire you to have more fun and be more optimistic in your daily routine, including in your current less than dreamy job.
A waitress aspiring to be an actress may get more tips for having a generally upbeat personality and brightening people's days with her optimism. Having an inspirational dream may help her earn more money in her current profession by inspiring her to a great attitude to whatever she does.
No matter how unrealistic your dream may seem, don't give it up until it no longer inspires you. Having a dream is a sign that you believe in yourself and can help you reinforce your self esteem, which is always practical in your current circumstances even if the dreams in your heart are not.
When your dream finally ceases to amaze and inspire you, it is time for a new dream. Until then, dream on!