Solved

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

Posted on 2011-09-05
6
1,076 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
2008 to 2016 SQL migration.. 5 29
Help in SQL Full Text Search 3 28
query help 18 51
Pivot not using aggregate yield error 3 21
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

759 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

21 Experts available now in Live!

Get 1:1 Help Now