Convert nvarchar(50) field to SQLSRV table datetime

Question

I need to change a field in the SQL database table whose format is in nvarchar(50) and data were saved in date format 07/09/2017, containing values NULL also! I need to convert this field directly into the database to datetime in format 2017-07-09 00:00:00.000 via T-SQL?

Answer 1

Thank you guys, I was able to convert the nvarchar field to datetime simply as follows:

UPDATE tb_Processo SET DataParcelaAntiga = TRY_CAST(DataParcelaAntiga AS DATETIME)

Answer 2

Create a new column with the new type, update it with the converted values, delete the old column, create again as DATETIME, update it with the values of the new column and delete this auxiliary column:

-- Cria a coluna auxiliar para recer um DATETIME
ALTER TABLE tb_processo ADD DataParcelaAntigaAUXILIAR DATETIME;
GO

-- Atualiza a coluna com o valor referente ao formato dd/MM/yyyy
UPDATE tp
   SET tp.DataParcelaAntigaAUXILIAR = CONVERT(datetime, tp.DataParcelaAntiga, 103)
  FROM tb_processos tp
 WHERE ISNULL(tp.DataParcelaAntiga, '') <> '';
GO

-- Elimina a coluna antiga
ALTER TABLE tb_processo DROP COLUMN DataParcelaAntiga;
GO

-- Recria a coluna antiga com o novo tipo
ALTER TABLE tb_processo ADD DataParcelaAntiga DATETIME;
GO

-- Atualiza o valor com base na coluna auxiliar
UPDATE tp
   SET tp.DataParcelaAntiga = tp.DataParcelaAntigaAUXILIAR
  FROM tb_processos tp
 WHERE ISNULL(tp.DataParcelaAntigaAUXILIAR, '') <> '';
GO

-- Elimina a coluna auxiliar
ALTER TABLE tb_processo DROP COLUMN DataParcelaAntigaAUXILIAR;
GO

---

The other way is to create the helper column, convert the values, delete the old one and rename the helper to get the same name as the old one:

-- Cria a coluna auxiliar para recer um DATETIME
ALTER TABLE tb_processo ADD DataParcelaAntigaAUXILIAR DATETIME;
GO

-- Atualiza a coluna com o valor referente ao formato dd/MM/yyyy
UPDATE tp
   SET tp.DataParcelaAntigaAUXILIAR = CONVERT(datetime, tp.DataParcelaAntiga, 103)
  FROM tb_processos tp
 WHERE ISNULL(tp.DataParcelaAntiga, '') <> '';
GO

-- Elimina a coluna antiga
ALTER TABLE tb_processo DROP COLUMN DataParcelaAntiga;
GO

-- Renomeia a coluna "DataParcelaAntigaAUXILIAR" da tabela "tb_processo" para "DataParcelaAntiga".
EXEC SP_RENAME 'tb_processo.DataParcelaAntigaAUXILIAR', 'DataParcelaAntiga', 'COLUMN';
GO

---

SP_RENAME

Changes the name of a user created object in the current database. This object can be a table, index, column, alias data type or data type CLR user-defined Microsoft .NET Framework Common Language Runtime.

Answer 3

You need to create a new column to receive your converted data and then delete the old column, after that just rename the new column.

An important detail in your select is the filtering of the data, as you have EMPTY fields, this can generate default values in the conversion.

Then you need to filter your fields NULL and the VAZIOS to make your conversion work without the minimum dates: 1900-01-01 00:00:00.000 what causes this is your EMPTY fields, but as there is no way to convert a NULL to a date we will filter them too.

declare @tb_processos table
(
    id int,
    DataParcelaAntiga nvarchar(50)
)


insert into @tb_processos values
(1, '27/02/2017'),
(2, '28/02/2017'),
(3, ''),
(4, null)

select * , CONVERT(datetime, DataParcelaAntiga, 103) from @tb_processos
where DataParcelaAntiga is not null
and len(DataParcelaAntiga) > 0

Your update would look like this.

UPDATE P
   SET P.DataAux = CONVERT(datetime, P.DataParcelaAntiga, 103)
   from tb_processos as P
   where P.DataParcelaAntiga is not null
and len(P.DataParcelaAntiga) > 0;