Converting NVARCHAR(255) to DATE

Question

I'm trying to transfer some old SQL Server data using Excel into SQL Server. It seems that Import/Export Data application automatically sets most data columns to NVARCHAR(255). Problem I have, is one of my columns is supposed to be a DATE type, but all data in it looks like this 18.08.2000 14:48:15.

So, when I try to use this query:

SELECT CONVERT(Date, DATE_TIME,  113)
FROM someTable

I get this error:

Msg 9807, Level 16, State 0, Line 1
The input character string does not follow style 113, either change the input character string or use a different style.

None of the [styles] from CAST and CONVERT (Transact-SQL) are working in my case.

Any advise or help is greatly appreciated.

SOLVED:

UPDATE myTable
SET columnName = CONVERT(NVARCHAR(255),CONVERT(SMALLDATETIME, columnName,105))
ALTER TABLE myTable
ALTER COLUMN columnName SMALLDATETIME

Solution 1

So, if it will ever become useful to anyone, this is the exact code that changes datatype NVARCHAR to DATETIME:

UPDATE myTable
SET columnName = CONVERT(NVARCHAR(255),CONVERT(SMALLDATETIME, columnName,105))
ALTER TABLE myTable
ALTER COLUMN columnName SMALLDATETIME

Solution 2

As far as I can tell - style no. 103 (British/French) should work - no?

DECLARE @input NVARCHAR(255)

SET @input = '18.08.2000 14:48:15'

SELECT CONVERT(DATETIME, @input, 103)

Gives me the output of:

2000-08-18 14:48:15.000

which seems pretty reasonable, no??

Solution 3

If you are using SQL Server 2012, try:

PARSE(Date AS datetime USING 'en-GB')

Or if that doesn't work, try:

PARSE(Date AS datetime USING 'Et-EE')

The Estonian culture specifically uses '.' as a date separator (d.MM.yyyy H:mm:ss) so it should work.

(Both seem to work fine on SQL Fiddle)