x

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

more ▼

asked May 04, 2010 at 09:06 AM in Default

Mohan gravatar image

Mohan
324 37 56 60

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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.

more ▼

answered May 04, 2010 at 10:09 AM

Blackhawk-17 gravatar image

Blackhawk-17
11.9k 28 31 36

+1 - spot on. Leave user interface stuff to the user interface.
May 04, 2010 at 10:25 AM Matt Whitfield ♦♦
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.
May 04, 2010 at 12:13 PM Fatherjack ♦♦
@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.
May 04, 2010 at 02:15 PM Blackhawk-17
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 04, 2010 at 09:18 AM

Grant Fritchey gravatar image

Grant Fritchey ♦♦
100k 19 21 74

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
May 04, 2010 at 09:28 AM Mohan
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.
May 04, 2010 at 09:35 AM Grant Fritchey ♦♦
thank you for your response...i will try with that
May 04, 2010 at 09:39 AM Mohan
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.
May 04, 2010 at 01:57 PM TimothyAWiseman
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.
May 04, 2010 at 02:08 PM Grant Fritchey ♦♦
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 04, 2010 at 09:09 AM

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

not at the time of fetching ...at the time of inserting i want to store the data in that format
May 04, 2010 at 09:12 AM Mohan
why, what advantage is there?
May 04, 2010 at 09:18 AM Fatherjack ♦♦
client requirement
May 04, 2010 at 09:33 AM Mohan
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered May 04, 2010 at 01:56 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

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.
May 04, 2010 at 04:03 PM Oleg
@Oleg, thank you. I stand corrected about how it stores it internally.
May 04, 2010 at 04:48 PM TimothyAWiseman
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1947
x94
x37

asked: May 04, 2010 at 09:06 AM

Seen: 4657 times

Last Updated: Mar 13, 2013 at 07:06 AM