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".
asked
how to change the date format of sqlserver 2005
simply query the data using the CONVERT function to style the values as you need them.
http://msdn.microsoft.com/en-us/library/aa226054%28SQL.80%29.aspx
You can use SET DATEFORMAT { format | @format_var } to control how dates are displayed within certain parameters.
Yep. DATEFORMAT just makes it so you don't have to use CONVERT all over the place. Setting the language can affect how it's displayed, but not how it's stored. It's going to store it any way it wants & there's nothing to be done.
What does it matter how the date is stored? All that matters is that a valid date gets stored. However, you can try setting the language which will change how dates are displayed.
I am glad you mentioned that. I hadn't seen that one before. Just to reemphasize though, that still affects how it is displayed by default, not how it is stored.
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.
Personally I dont think the customer actually wants(cares) what the storage format is, nor would they look/check. I think its a statement in a database/system specification that has been taken literally. The customer wants to see the date in the requested format and has used the word 'stored', that has then been taken literally from a DBA perspective and is proving to be a red-herring in the database development.
+1 - spot on. Leave user interface stuff to the user interface.
@Fatherjack - Most likely that's the case... but it can still be done - that is what I was alluding to... just that overhead far outweighs ability.
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.]
Timothy is absolutely correct, you cannot control how your data is stored assuming you want to preserve the datetime data type. I just wanted to add a small correction to the statement that datetime is a float value. Actually, it is not. Datetime needs 8 bytes ( 2 ints) to store data. First int is used to count the number of days from 1900-01-01, and the second int is used to store the number of ticks from midnight of current day. This is why select cast(0 as datetime) returns '1900-01-01' and select cast(-53690 as datetime) returns '1753-01-01', which is the smallest possible datime value.
@Oleg, thank you. I stand corrected about how it stores it internally.
1 Person is following this question.
