Solved

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

Posted on 2011-09-05
6
1,081 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:JameMeck
6 Comments
 
LVL 4

Accepted Solution

by:
Sudhanshupathak earned 250 total points
ID: 36486479
0
 
LVL 22

Expert Comment

by:Thomasian
ID: 36486511
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
/*Result:
2011-01-31 00:00:00.000			01/31/2011
*/

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36486760
Cast (Datefield as datetime)
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 9

Expert Comment

by:mimran18
ID: 36486818
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

0
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 36488904
please see this article to work correctly with dates (and strings that look like dates):
http://www.experts-exchange.com/A_1499.html
0
 

Author Closing Comment

by:JameMeck
ID: 36507526
Thanks! I got it now.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

     When we have to pass multiple rows of data to SQL Server, the developers either have to send one row at a time or come up with other workarounds to meet requirements like using XML to pass data, which is complex and tedious to use. There is a …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now