converting varchar to datetime in spacific format

Question

Post reply

converting varchar to datetime in spacific format

rshm35

SSC-Addicted

Points: 496
More actions
June 16, 2010 at 8:36 am

#220070

Hi I have a date in varchar(yyyy/mm/dd)
I need to convert to datetime in the format mm/dd/yyyy
can u suggest me any thing
I am using the convert(datetime,ItemDate,101 ) but i'm not able to.
Thanks in advance

Viewing 4 posts - 1 through 3 (of 3 total)

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

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

DECLARE @date AS VARCHAR(10)

SET @date = '2010/01/29'

SELECT CONVERT(VARCHAR(10), (CONVERT(DATETIME, @date)), 101)

--OUTPUT -> "01/29/2010"

??

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

rshm35 SSC-Addicted Points: 496 More actions · Answer 2

I need that to be in datetime only

and change the format

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

to convert string YYYY/MM/DD (which is close to safe ISO8601 format) into datetime you don't need anything other than:

SET DATEFORMAT ymd

DECLARE @date AS VARCHAR(10)

SET @date = '2010/01/29'

SELECT CONVERT(DATETIME, @date)

"SET DATEFORMAT ymd" will not be required if your default DATEFORMAT is not dmy. Also, values in ISO or ISO8601 format are safe to be converted into datetime independently of the DATEFORMAT settings.

Now, about having or storing the datetime in a particular format:

IT IS IMPOSSIBLE!

Or better to say is not a correct request.

The DATETIME is a DATETIME.

When you are talking about "I want datetime in a specific format" - you are talking about string representation of a datetime for display purposes. Check options for CONVERT function to see in which format you can output DATETIME values in SQL Server.

Without specific format, when you select a DATETIME, it will be formatted as per default which is "mon dd yyyy hh:miAM (or PM)" (its depends on your locale and database language settings which can be changed).

_____________________________________________
"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]