How can I format a datetime column in SQL Server

Hi Experts,
I have a datetime column in SQL that saves the date like 2013-09-16 00:00:00.000.  If want the data to be saved in this format 'mm/dd/yyyy', how can I do this?

Thanks in advance,
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Use convert function. You will need to use 101 as a format


Open in new window

Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Observer the Select Convert (varchar(10),Getdate(),101) above will give you a varchar, and not a date.

You really SHOULD store the date as a date in your database. Comparison, between and other TSQL code will run optimized, and it takes up much less space.

Perhaps you could use the smalldatetime datatype wich takes up four bytes. A Varchar(10) takes up 12 bytes ( If you go for not storing dates as dates, at least use Char(10), wich takes up 10 bytes.

In a case using smalldatetime you would save 2/3 of the space, i e 4 bytes instead of 12.

Then when you SELECT your data, you do the conversion as:
SELECT CONVERT(VARCHAR(10), GetDate(), 101)  to DISPLAY the data as you wish.

To read more on dates look at Tibor Karaszi's Date page:

Regards Marten
>>"I have a datetime column in SQL"
this we can accept as a fact

>>" that saves the date like 2013-09-16 00:00:00.000."
this I'm afraid is not true, if the fact above is true

From memory I think Tibor states this quite well: it is a common misconception that datetime information is stored in a human readable format. This is simply not true because the way SQL Server actually stores datetime is as a pair of integers - one for that date component and another for the time component. As far as the database is concerned datetime information is a special numeric.

We can easily be forgiven for this misunderstanding because we input dates in a human readable form, and we look at output in a human readable format - but they are NOT stored that way at all.

The answer to your conundrum is that you need to alter the output format of that datetime you can do this with the convert() function,
or if you happen to use SQL 2012 you can use FORMAT([the_datetime],'mm/dd/yyyy')

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Thank you.  Cheers, Paul

My reference tables of date sql server styles might be useful perhaps.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.