Date convertion

Question

Date convertion

karunakar2351

Right there with Babe

Points: 729
More actions
July 22, 2013 at 5:58 am

#276222

Hi,
My table have a column with date format 'Jun 15 2010 12:00:00:000AM'
But i need that column name with the format of 2010-06-15 so what i will do Plz help me...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

Why do you want to format your dates in your table? You should store them as dates and format them until you display them.

Is this a datetime column? or a char/varchar?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

if your column is of DATETIME or DATE datatype, it doesn't have any format. So you cannot change the format of it!

The way SQL Server stores dates internally has nothing to do with the representing dates on a client (eg. SSMS).

What you can do is to show the date in a format you want when queried your data (eg. in SSMS)

To enforce format for the DATE and DATETIME values, (until AQL2012) you can use T-SQL CONVERT function.

Check it's options here:

http://msdn.microsoft.com/en-us/library/ms187928(v=sql.100).aspx

Just use the style you need...

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

karunakar2351 Right there with Babe Points: 729 More actions · Answer 3

karunakar2351

Right there with Babe

Points: 729

July 22, 2013 at 6:55 am

#1634445

The datatype of the column is varchar(50)

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 4

karunakar2351 (7/22/2013)
The datatype of the column is varchar(50)

Obviously very wrong choice for storing dates, as it allows to contain any string.

Now, the best way to fix all possible issues with that is to change it to DATE or DATETIME (if you need time part). Then, whenever you want to extract the data from it, you will be free to format it as you like.

If you cannot change this column, add new computed column and us ISDATE and CONVERT function to make sure it will contain valid date. Then, in your queries, you will be able to use this column with confidence that it does contain a valid date value.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

karunakar2351 (7/22/2013)
The datatype of the column is varchar(50)

Why? That's a question for you to ask, not answer. Are there any benefits at all in having this column typed as string rather than date?

To answer your original question; use CONVERT to DATETIME or DATE, then CONVERT again to the string format you require. Use conversion codes in both directions.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden