By default in sqlserver date will be stored in "yyyy-mm-dd" format, but as per my requirement I need to change the format to "mm-dd-yyyy".
simply query the data using the CONVERT function to style the values as you need them.
You can store it as a VARCHAR instead but then you will lose all the advantages of the built-in DATETIME manipulation functions. You can also add a computed column in the table to do the conversion from DATETIME to VARCHAR and have it stored to meet the Client requirements.
Seriously though, what is being asked for is cosmetic and not a SQL best practice in any way.
Remember that a datetime is actually a pair of integer values that is interpreted as a date and time. You really cannot control how it is stored (if kept as datetime), it is forward from the reference date and from midnight for the time.
Now, as Blackhawk mentioned, you can abandon datetime and use varchar, but then you aren't really storing dates at all, just character strings.
The better answer, and likely what the client really cares about, is how it is displayed. You can control that using convert. Remember that you can include that "convert" in the code for a view and let the client query the view. Also, you can include a computer column that just runs the appropriate convert on your real data as well. That way the client will only ever see it as a varchar displayed in the requested format, but you have the advantages of storing it as a real datetime.
Perhaps the best answer is the pass it as a datetime to the gui and let the gui worry about formatting it. The formatting options for formatting a datetime in Python/C#/etc are generally much more developped then the limited options availabe in the convert command. If this does not meet your clients requirements though, then I would look at using convert and hiding that convert behind either a computed column or a view.
[Edit: As caught by Oleg, I corrected the descript of how it is stored internally. I apologize for the initial error.]
1 Person is following this question.