Convetir nvarchar to datetime

Question

Convetir nvarchar to datetime

Navigation

#1 by (1 votes)
#2 by (0 votes)

2

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:

20140120201508

Representing yyyymmddHHmmSS .

How can I make this conversion?

I am using SQL server 2012.

Thanks in advance ...

sql-server

asked by Michel perez ogando 29.07.2016 в 19:09

source

2 answers

I need to Refresh a View When using onchage in a select. in MVC 4 razor C # hashtable classes and hashMap help! [duplicate]

score 1 · Answer 1

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 )

score 0 · Answer 2

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