[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Need SQL help to convert nvarchar(255) to datetime.

Posted on 2013-05-15
3
Medium Priority
?
656 Views
Last Modified: 2013-05-16
I hope this posting finds everyone doing well.

I received a SQL table with a nvarchar(255) column with dates as strings, below are a few examples.

Sun Aug 19 19:01:17 +0000 2012
Thu Oct 13 21:35:22 +0000 2011
Fri Apr 13 03:24:38 +0000 2012
Mon Nov 28 03:05:30 +0000 2011
Wed May 27 01:30:07 +0000 2009
Fri Jul 22 00:27:20 +0000 2011

I wanted to see if anyone knew how to convert these to datetime.  I have been trying the convert method with no luck.  I figure I would ask on here before I started writing my own SQL function to do this.

Many thanks!
0
Comment
Question by:rye004
3 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 500 total points
ID: 39169671
declare @dt varchar(255) = 'Sun Aug 19 19:01:17 +0000 2012'
select cast (RIGHT(@dt,4)+ ' ' +substring(@dt,5, len(@dt)-14) as datetime )
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 500 total points
ID: 39172083
If you don't need the offset -- for example, it's always +0000 anyway, you can do this:

    CAST(  RIGHT(string, 4) + SUBSTRING(string, 4, 17)  ) AS datetime
0
 

Author Closing Comment

by:rye004
ID: 39173031
Thank you so much for your fast response.  These both worked perfectly.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Suggested Courses

834 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