MS SQL 2008 R2, convert datetime in string to datetime format.

I have a column with string format: dd/mm/yyyy
Now I want to convert it to datetime format: mm/dd/yyyy

How can I do?
Who is Participating?
SudhanshupathakConnect With a Mentor Commented:
DECLARE @columnname varchar(100)
SET @columnname = '31/1/2011'
SELECT CONVERT(datetime, @columnname, 103)		--returns datetime datatype
      ,CONVERT(varchar,CONVERT(datetime, @columnname, 103),101)		--returns varchar datatype
2011-01-31 00:00:00.000			01/31/2011

Open in new window

Alpesh PatelAssistant ConsultantCommented:
Cast (Datefield as datetime)
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Try this
Declare @sdate as datetime
set @sdate='2011-12-15'  --15th December 2011

Select Convert(nvarchar(11),@sdate,101) as [mm/dd/yyyy]
,Convert(nvarchar(11),@sdate,103) as [dd/mm/yyyy] from [test]

Open in new window

Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
please see this article to work correctly with dates (and strings that look like dates):
JameMeckAuthor Commented:
Thanks! I got it now.
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.

All Courses

From novice to tech pro — start learning today.