CODEWITHSUNDEEP
sql-serversql-server-2008
Hiren gardhariya
Hiren gardhariya

Reputation: 1257

convert nvarchar to smalldatetime in sql server

I have one table in which nvarchar data are there.

Table
18-FEB-11 10.29.52.000000000 AM
20-FEB-11 04.10.40.000000000 PM
23-SEP-10 10.34.57.714000000 AM
08-OCT-10 09.41.16.921000000 PM

I want to convert this field to small date time in sql server 2008 R2

How can i convert it.

Expected OUTPUT:

2011-02-18 10:29:52
2011-02-20 16:10:40
2010-09-23 10:34:57
2010-10-08 21:41:16

Upvotes: 0

Views: 2480

Answers (2)

user4622594
user4622594

Reputation:

this should work too:

DECLARE @datestring NVARCHAR(50) = '08-OCT-10 09.41.16.921000000 PM'
SELECT CAST(REPLACE(LEFT(@datestring,18),'.',':') + RIGHT(@datestring,2) AS SMALLDATETIME)

Upvotes: 1

A Ghazal
A Ghazal

Reputation: 2833

Try this please

declare @s_date as nvarchar(100)= '18-FEB-11 10.29.52.000000000 AM'
SELECT CONVERT(nvarchar(100),CAST(REPLACE(REPLACE(@s_date, SUBSTRING(@s_date, 19, 10),''),'.',':') as datetime), 120)

result : 2011-02-18 10:29:52

Upvotes: 3

Related Questions