How can I format a datetime column in SQL Server

Posted on 2013-09-16
Medium Priority
Last Modified: 2013-10-06
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,
Question by:mainrotor
  • 2
LVL 25

Expert Comment

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


Open in new window

LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 1000 total points
ID: 39498140
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 (http://technet.microsoft.com/en-us/library/ms176089.aspx). 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
LVL 49

Accepted Solution

PortletPaul earned 1000 total points
ID: 39498207
>>"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')
LVL 49

Expert Comment

ID: 39551368
Thank you.  Cheers, Paul

My reference tables of date sql server styles might be useful perhaps.

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question