Convetir nvarchar to datetime


I have a table with many records, when the table was created a type field nvarchar was used to store the date and time (I do not know why datetime was not used) the case is that I need to convert those values into datetime and I could not do neither with convert nor with cast , the values are saved in the following format:


Representing yyyymmddHHmmSS .

How can I make this conversion?

I am using SQL server 2012.

Thanks in advance ...

asked by Michel perez ogando 29.07.2016 в 19:09

2 answers


You can perform the conversion using the following query (Assuming your column is called "date"):

  Select convert(datetime,((left(fecha,8))+' ' + substring(fecha,9,2) 
  + ':' + substring(fecha,11,2) + ':' + substring(fecha,13,2) + '.000'),113) from Tabla 

But if you want to extract separately the year, month, day, hour, minute and second, you would have to put the type in the DATEPART sentence, for example for YEAR:

Select datepart(YEAR, convert(datetime,((left(fecha,8))+' ' 
+ substring(fecha,9,2) + ':' + substring(fecha,11,2) + ':' 
+ substring(fecha,13,2) + '.000'),113)) from Tabla

You just have to change the YEAR type to the one you want ( Month, Day, Hour, Minute, Second )

answered by 30.08.2016 в 18:14

Welcome to SOes

/*Obtenemos todos los datos por separado*/
declare @anio nvarchar(4) = substring('20140120201508',1,4)
declare @mes nvarchar(2) = substring('20140120201508',5,2)
declare @dia nvarchar(2) = substring('20140120201508',7,2)
declare @hora nvarchar(2) = substring('20140120201508',9,2)
declare @minuto nvarchar(2) = substring('20140120201508',11,2) 
declare @segundo nvarchar(2) = substring('20140120201508',13,2)
/*Le damos el formato que buscamos*/
declare @cadenaAFormatear nvarchar(20) = concat(@anio,'-',@mes,'-', @dia,' ',@hora,':',@minuto,':',@segundo)
select FORMAT(cast(@cadenaAFormatear as datetime),'yyyy-MM-dd HH:mm:ss') as FechaFormateada
/*Sin tantas variables*/
declare @cadenaAFormatear2 nvarchar(20) = concat(substring('20140120201508',1,4),'-',substring('20140120201508',5,2),'-', substring('20140120201508',7,2),' ',substring('20140120201508',9,2),':',substring('20140120201508',11,2) ,':',substring('20140120201508',13,2))
select FORMAT(cast(@cadenaAFormatear2 as datetime),'yyyy-MM-dd HH:mm:ss') as FechaFormateada2

For more information about working with the date formats, visit link

answered by 29.07.2016 в 20:16