• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 685
  • Last Modified:

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

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
rye004
Asked:
rye004
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
rye004Author Commented:
Thank you so much for your fast response.  These both worked perfectly.
0
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Tackle projects and never again get stuck behind a technical roadblock.
Join Now