question

Mohan avatar image
Mohan asked

how to change the date format of sqlserver 2005

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".

sql-server-2005datetimedatatypes
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

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

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

not at the time of fetching ...at the time of inserting i want to store the data in that format
0 Likes 0 ·
why, what advantage is there?
0 Likes 0 ·
client requirement
0 Likes 0 ·
Grant Fritchey avatar image
Grant Fritchey answered

You can use SET DATEFORMAT { format | @format_var } to control how dates are displayed within certain parameters.

5 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
1 Like 1 ·
sample table structure crate table emp(dob datetime) insert into emp values ('2009/05/04') select * from emp it will give the result as 2009-05-04 my requirement is, in the table itself it has to store as "mm/dd/yyyy" format instead of default date format of sqlserver
0 Likes 0 ·
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.
0 Likes 0 ·
thank you for your response...i will try with that
0 Likes 0 ·
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.
0 Likes 0 ·
Blackhawk-17 avatar image
Blackhawk-17 answered

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.

3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
+1 - spot on. Leave user interface stuff to the user interface.
0 Likes 0 ·
@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.
0 Likes 0 ·
TimothyAWiseman avatar image
TimothyAWiseman answered

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.]

2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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.
2 Likes 2 ·
@Oleg, thank you. I stand corrected about how it stores it internally.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.