How to convert varchar to datetime in SQL Server?

You can do it in a simple way as follows:

SET LANGUAGE Spanish; 

SELECT CAST(REPLACE('14 de Febrero 1986',' de ',' ') AS DATETIME) fecha; 

fecha
14.02.1986 00:00:00

Optimal solution for SQL Server 2012 +

If you are working with SQL Server 2012+, you can implement TRY_CAST , which will give you a value NULL in the wrong data. It can be an interesting resource when normalizing the values, passing them to a column of type DATETIME , which would be the most correct. Well, when you still have the data of the column currently declared as VARCHAR , you can sort alphabetically by the new column produced by TRY_CAST , correct the wrong values, apply the query again and have in a simple way the normalized data and No risk of error.

The query with TRY_CAST would be identical to the query mentioned in point 2.

SELECT TRY_CAST(REPLACE('14 de Febrero 1986',' de ',' ') AS DATETIME) fecha; 

Result:

fecha
14.02.1986 00:00:00

Let's see the result about a hypothetical wrong value:

SELECT TRY_CAST(REPLACE('14 de Febrer 1986',' de ',' ') AS DATETIME) fecha; 

Result:

fecha
NULL

The problem you have is that the string has a format that does not seem to match some of the date conversion options of CAST or CONVERT . What you can do is work the chain a bit to take it to a more compatible format. For example:

DECLARE @Cadena VARCHAR(100)
SELECT  @Cadena = '14 de Febrero 1986'

DECLARE @Meses TABLE  (
    nombre VARCHAR(15),
    numero VARCHAR(2)
)

INSERT INTO @Meses (nombre, numero) VALUES ('Enero','01')
INSERT INTO @Meses (nombre, numero) VALUES ('Febrero','02')
INSERT INTO @Meses (nombre, numero) VALUES ('Marzo','03')
INSERT INTO @Meses (nombre, numero) VALUES ('Abril','04')
INSERT INTO @Meses (nombre, numero) VALUES ('Mayo','05')
INSERT INTO @Meses (nombre, numero) VALUES ('Junio','06')
INSERT INTO @Meses (nombre, numero) VALUES ('Julio','07')
INSERT INTO @Meses (nombre, numero) VALUES ('Agosto','08')
INSERT INTO @Meses (nombre, numero) VALUES ('Septiembre','09')
INSERT INTO @Meses (nombre, numero) VALUES ('Setiembre','09')
INSERT INTO @Meses (nombre, numero) VALUES ('Octubre','10')
INSERT INTO @Meses (nombre, numero) VALUES ('Noviembre','11')
INSERT INTO @Meses (nombre, numero) VALUES ('Diciembre','12')

SELECT  @Cadena = REPLACE(@Cadena, 'de ', '')
SELECT  @Cadena = REPLACE(@Cadena,nombre,numero)
    FROM @Meses

SELECT  @Cadena = REPLACE(@Cadena, ' ', '-')
SELECT  @Cadena = RIGHT('0'+SUBSTRING(@Cadena, 1, CHARINDEX('-', @Cadena)-1),2) + SUBSTRING(@Cadena, CHARINDEX('-', @Cadena) , LEN(@Cadena))

-- Resultado final
SELECT  CONVERT(DATETIME, @Cadena, 105)

Basically a) we remove the text de , b) we replace the names of the months by their numerical representation, c) we add the scripts to separate each part of the date and d) we convert to datetime using the format 105 .

Another possibility, much better but only if you had a version of SQL Server 2012 or higher, is to use PARSE in the following way:

DECLARE @Cadena VARCHAR(100)
SELECT  @Cadena = '14 de Febrero 1986'

SELECT PARSE(@Cadena AS datetime USING 'es-ES') AS Result

Another method:

declare @Fechas table (Fecha varchar(30) )
insert into @Fechas
values 
        ('14 de Enero 1986'),
        ('1 de Febrero 1986'),
        ('3 de Marzo 1986'),
        ('23 de Abril 1986'),
        ('6 de Mayo 1986'),
        ('11 de Junio 1986'),
        ('30 de Julio 1986'),
        ('19 de Agosto 1986'),
        ('24 de Septiembre 1986'),
        ('16 de Octubre 1986'),
        ('27 de Noviembre 1986'),
        ('15 de Diciembre 1986')

select  reverse(substring(reverse(Fecha), 1, 4)) + '-' +
        case
            when Fecha like '%ENE%' then '1'
            when Fecha like '%FEB%' then '2'
            when Fecha like '%MARZ%' then '3'
            when Fecha like '%ABR%' then '4'
            when Fecha like '%MAY%' then '5'
            when Fecha like '%JUN%' then '6'
            when Fecha like '%JUL%' then '7'
            when Fecha like '%AGOS%' then '8'
            when Fecha like '%SEPT%' then '9'
            when Fecha like '%OCT%' then '10'
            when Fecha like '%NOV%' then '11'
            when Fecha like '%DIC%' then '12'
        end
        + '-' + substring(Fecha, 1,  charindex(' ', Fecha)) 
from @Fechas