CODEWITHSUNDEEP
sqlsql-serverexceldatetimesql-server-2008-r2
Shiva Naru
Shiva Naru

Reputation: 1205

Convert date in excel of the format "25-OCT-13 01.08.24.732000000 PM" to DateTime in Sql Server 2008 R2

I have a varchar date of the format "25-OCT-13 01.08.24.732000000 PM" (this has been read from excel file). Now I need to convert this to DateTime type in Sql Server 2008 R2.

I checked the Convert() function of Sql Server but this format does not match any of the existing formats e.g. 101 or 120, etc. i.e. Convert(DateTime, thisweirdDate, 120) did not work.

Thanks.

Upvotes: 1

Views: 217

Answers (1)

Lamak
Lamak

Reputation: 70658

Ok, there has to be a better way to do this, but here is one way:

DECLARE @DataExample VARCHAR(31)
SET @DataExample = '25-OCT-13 01.08.24.732000000 PM'

SELECT  CONVERT(DATETIME,CONVERT(VARCHAR(8),[Date],112)+
        ' '+CONVERT(VARCHAR(12),[Time]))
FROM (
SELECT  CONVERT(DATE,REPLACE(LEFT(@DataExample,9),'-',' '),6) [Date],
        CONVERT(TIME,REPLACE(SUBSTRING(@DataExample,11,12),'.',':')+
                     RIGHT(@DataExample,2)) [Time]
) A

Upvotes: 2

Related Questions