CODEWITHSUNDEEP
t-sqldatetimesql-server-2008-r2varchar
Barrett Chamberlain
Barrett Chamberlain

Reputation: 129

Extract portion of date string and convert to datetime in T-SQL

I'm running SQL Server Standard 2008 R2 on a 64 bit version of Windows Server 2008 R2 standard (sp1)

I've imported a log file as a flat file source. One of the columns from the import called col2 in the table called big holds values like this: 16/Mar/2007:11:30:17 as varchar(50).

I want to convert that column (col2) to a datetime datatype.

One method I was trying was to extract each part of the date string and then recombine and convert them.

The problem I ran into is that each column has different widths since the log file couldn't be neatly delimited, making using something like CHARINDEX return a single digit or sometimes NULL.

I've been attempting to set up using regex using CLR integration but can't get it to work (I can't create a C# project in Visual Studio, there's no option for it) and Master Data Services won't install because SQL Server 2008 R2 Standard doesn't support it.

What is my best method to do this? Using CASE, SUBSTRING and CHARINDEX?

Upvotes: 1

Views: 1737

Answers (4)

AnandPhadke
AnandPhadke

Reputation: 13506

try this>

select convert(datetime,STUFF(big,CHARINDEX(':',big),1,' '),101) from yourtable

Example:Check here as datatime string format.If its the same it will work

DECLARE @d varchar(50) = '16/Mar/2007:11:30:17'
select convert(datetime,STUFF(@d,CHARINDEX(':',@d),1,' '),101)

Upvotes: 0

Niladri Biswas
Niladri Biswas

Reputation: 4171

Though I liked the idea of EricZ, however, here is my solution

DECLARE @d varchar(50) = '16/Mar/2007:11:30:17'
SELECT CAST( LEFT(@d,PATINDEX('%:%',@d) - 1) + ' ' +  SUBSTRING(@d,PATINDEX('%:%',@d) + 1,LEN(@d)) AS DATETIME)

Upvotes: 2

EricZ
EricZ

Reputation: 6205

Try this

DECLARE @d varchar(50) = '16/Mar/2007:11:30:17'

SELECT CAST(STUFF(@d,CHARINDEX(':',@d),1,' ')  AS DATETIME)

Upvotes: 3

paparazzo
paparazzo

Reputation: 45096

select convert(datetime, 
       SUBSTRING('16/Mar/2007:11:30:17', 0, CHARINDEX(':', '16/Mar/2007:11:30:17')) + ' ' +
       SUBSTRING('16/Mar/2007:11:30:17', CHARINDEX(':', '16/Mar/2007:11:30:17') + 1, 8) , 1)

Upvotes: 1

Related Questions