Convert nvarchar field (50) into datetime table SQLSRV

1

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

    
asked by anonymous 07.09.2017 / 16:14

3 answers

1

Create a new column with the new type, update it with the converted values, delete the old column, create it again as DATETIME , update it with the new column values 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 auxiliary column, convert the values, delete the old one and rename the auxiliary 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, data type of alias, or data type CLR defined by user Microsoft .NET Framework Common Language Runtime .

    
08.09.2017 / 14:49
1

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

UPDATE tb_Processo SET DataParcelaAntiga = TRY_CAST(DataParcelaAntiga AS DATETIME)
    
08.09.2017 / 03:21
1

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

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

So you need to filter your NULL and VAZIOS fields so that your conversion works fine without the minimum dates: 1900-01-01 00:00:00.000 What causes this are your EMPTY fields, but since you can not 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;
    
11.09.2017 / 13:41