Converter Texto em Data

[gustavo1982]

Colegas eu estou com uma questao a ser resolvida num banco de dados que recebi para alterar um campo do tipo texto nvarchar(255) chamado 'datacriacao' que está na formatação '2016-10-23 20:44:11' para data. Como não domino SQL, não sei como fazer esta conversao. Até tentei algumas syntaxes mas não consegui resolver.

[Bergamaschi.]

Que banco de dados é? MySQL?

Já que as datas estão no formato certo (YY-MM-DD etc), você pode transforma-lo em um datatime de boa.

Faz um alter table. Seria algo como:

ALTER TABLE tabela CHANGE (ou MODIFY não me lembro ao certo) nomedocampo  nomedocampodenovo datatime NULL/NOT NULL

É isso que você queria saber?

[DiF]

Só para corrigir, é datetime, e não datatime

[gustavo1982]

o banco é SQL Server 2005. Mas como é a syntax? Aqui aparece uma barra de rolagem pros lados que ue mexo mas nao vejo o codigo.

Eu apliquei o codigo que eu usei a um tempo atras pra mudar a data de texto pra date numa tablea de um projetinho e loja de cd´s que era assim:

SELECT CAST('11/02/2012 14:51:59' AS DATETIME) LANCAMENTO from CD

{LANCAMENTO é a coluna da data e CD é o nome da tabela do CD do artista}

SELECT CAST('11/02/2012 14:51:59' AS DATETIME) datacriacao from pcerj_2012_outnov_2

{datacriacao é a colunda data que esta como texto e que venho tentar alterar pra data e pcerj_2012_outnov_2 é a tabela que eu espelhei a partir da original pra ir testando os comandos de conversão até achar um que funcione}

acontece que a resposta do select cast ele devolve todos as linhas da tabela com o que está entre aspas escrito dentro delas e o que eu quiria era converter os campos, nao escrever a mesma coisa em todos eles rsrsrsrs

Eu tentei novamente escrevendo assim:

SELECT CAST(datacriacao AS DATETIME) from pcerj_2012_outnov_2

e ele deu erro informando: Arithmetic overflow error converting expression to data type datetime.

Se eu tentar um ALTER TABLE ele diz que o nome do campo nao é um objeto valido. Admito que estou patinando mesmo...

[LNW]

Gustavo,

Colegas eu estou com uma questao a ser resolvida num banco de dados que recebi para alterar um campo do tipo texto nvarchar(255) chamado 'datacriacao' que está na formatação '2016-10-23 20:44:11' para data. Como não domino SQL, não sei como fazer esta conversao. Até tentei algumas syntaxes mas não consegui resolver.

Tente fazer:

[B]ALTER TABLE[/B] [I]tabela[/I] [B]ALTER COLUMN[/B] [I]coluna[/I] [B][U]novo_tipo[/U][/B]

[B]ALTER TABLE[/B] [I]pcerj_2012_outnov_2[/I] [B]ALTER COLUMN[/B] [I]datacriacao[/I] [U][B]DATETIME[/B][/U]

LNW

[gustavo1982]

Ainda apresenta o erro "Msg 8115, Level 16, State 2, Line 1

Arithmetic overflow error converting expression to data type datetime.

The statement has been terminated.":wacko:

Aos poucos eu vou descobrindo esses comandos de alteração da tabela. pra mim era só o cast e o convert, nao sabia do alter table. Ainda que nao tenha funcionado, ta servindo de aprendisado^_^

Mas esse overflow será em função do que? do campo nvarchar ter tamanho 255?

[Douglas Turque]

gustavo1982,

Se você está rodando:

SELECT CAST('11/02/2012 14:51:59' AS DATETIME)

e está OK; Enquanto que quando você roda:

SELECT CAST(datacriacao AS DATETIME) from pcerj_2012_outnov_2

e dá erro, significa que nem todas as linhas da coluna datacriacao estão no formato dd/mm/aaaa hh:mm:ss. Como o tipo de dados da coluna está como nvarchar(255) acredito que deve ter sujeira* aí no meio.

*sujeira: algumas linhas devem ter dados inconsistentes.

Rode estes 2 códigos abaixo por favor, cole os resultados em 2 arquivos .txt (1 arquivo para cada código) e anexe ao tópico:

SELECT TOP 1000 datacriacao FROM pcerj_2012_outnov_2 order by datacriacao

SELECT TOP 1000 datacriacao FROM pcerj_2012_outnov_2 order by datacriacao desc

OBS: Apesar de parecidos, os códigos são diferentes. rode os 2 separadamente.

O objetivo em rodar esses 2 códigos é para verificarmos se não há inconsistências de dados na coluna.

Outra observação importante é, repare que quando voce roda:

SELECT CAST('11/02/2012 14:51:59' AS DATETIME)

Ele está trocando os dias pelo mês, ou seja, o '02' está virando dia e o '11' está virando mês.

[gustavo1982]

Esse TOP 1000 ja vai da ideia do problema? Pergunto isso, porque a tabela em sí tem quase 130 mil linhas.

Mas uma coisa que me chamou atenção, é que voce levantou a possibilidade de haver sujeira no datacriacao. Então preciso explicar uma coisa. Este campo é uma concatenação de dois outros campos de uma outra tabela que subdivide-se em datc, contendo dia do ano do tipo data(date,null) e horc, contendo hora hh:mm:ss do tipo texto(nvarchar(10),null). Eu notei que alguns campos datc e horc estão null então daí que deve ter vindo a sujeira. Será?

Eu rodei o seguinte comando:

update pcerj_2012_outnov_2

set pcerj_2012_outnov_2.datacriacao = cast(datc as smalldatetime) + ' ' + horc

from pcerj_2012_outnov_2

inner join pcerj_2012_outnov

on pcerj_2012_outnov_2.num = pcerj_2012_outnov.rgocronu

E o mesmo executou a conversão indexado pelos campos num/rgocronu mas que gerou o campo datacriacao do qual não consigo rodar nenhuma busca no banco pelo software de Geoprocessamento que usamos aqui porque a formatação está assim, ex.: "11/02/2012 14:51:59.000"

pcerj_2012_outnov é a tabela original da qual eu preciso filtrar os campos úteis, dropar os que não servem e converter alguns deles para o tipo correto, tais como ex.: idade que está como float e precisa ser integer, área que esta como nvarchar(10) e precisa ser interger e o campo datc e horac que precisam ser concatenados, já que o dia e a hora de criação desses registros precisa estar discriminado num campo só (regra de negocio imposta ao fazer buscas pelo software de geoprocessamento que usamos).

Agora to ainda mais angustiado, porque além de estar ainda mais confuso, eu mesmo devo estar deixando a cabeça de vocês ainda mais desnorteados. Fico ate com vergonha de fica pedindo ajuda a vocês da comunidade, visto o problema que eu mesmo criei ao tentar fazer a conversão por conta propria...

[Douglas Turque]

Então gustavo,

Se algum dos campos concatenados estiverem NULL, não tem problema porque

SELECT NULL + qualquer_outra_coisa = NULL

Porém se tiver alguma data ou hora '' ele traria algo como ' 13:40:45' ou '12/02/2012 ' com espaços.

Gustavo, se puder rodar aqueles SELECTs TOP 1000, ajudaria bastante. Não tem problema se a tabela tem 130 mil registros pois a gente tá usando o ORDER BY em cada um reverso, ou seja, ele vai pegar os TOP 1000 maiores e os TOP 1000 menores.

Outra observação é que NULL é diferente de '', se tiver linhas com '' pode ser um problema também.

[gustavo1982]

Eu anexei os TXT com a resposta do SQL aos dois comandos que voce instruiu, o problema é que eles foram feitos depois deu ter aplicado o comando de conversão de texto pra data que eu rodei fim de semana e que adicionou um ".000" ao final de todas as datas...

Bom, de qualquer forma eu vou fazer outra tabela com outro campo datacriacao sem estar alterado. Admito que manipular tabelas que não contém informações somente do tipo texto é um tanto delicado. Lembro da época da faculdade em que todas as provas era de tableas que continham somente texto(época boa, mas vejo hoje que isso nao me preparou adequadamente pra problemas assim.)

Desde já agradeço a demonstração de interesse nessa questão. Obrigado. Aliás, obrigado a todos os colegas até agora, claro.

[Douglas Turque]

Gustavo,

Pelo que eu vi dos arquivos .txt parece que não há sujeira na coluna. Acredito que após esse UPDATE você consiga fazer o que você deseja. Se você quiser mudar na estrutura da tabela o datatype para datetime, use o código abaixo:

ALTER TABLE	NOME_DA_TABELA
ALTER COLUMN	NOME_DA_COLUNA DATETIME	NULL

Se quiser apenas visualizar como DATETIME use algo como:

SELECT
		CONVERT(DATETIME, datacriacao) 
FROM pcerj_2012_outnov_2

[gustavo1982]

Obrigado pelas dicas, foram muito úteis. Uma última pergunta, caso saiba, é que tem como tirar esse ".000"? Isso me parece ser algum padrão de notação de datas, sabe.

Um ultimo detalhe, da pra usar a resposta do select para dar insert numa outra coluna?

Obrigado novamente, prometo que não vou ficar mais alugando, até porque eu ja estou entendendo melhor conseguindo o que queria saber.

[Douglas Turque]

Gustavo,

Fique a vontade para fazer perguntas, você não tá alugando ninguém, todo mundo tá aqui pro vontade própria hahahahaha

Quanto às suas perguntas, dá pra fazer o INSERT sim usando um SELECT. Você pode fazer algo do tipo:

INSERT INTO tabela2 (coluna1, coluna2)
SELECT
CONVERT(date, datacriacao),
colunaX
FROM   tabela1

Importante lembrar que quando você fizer um INSERT com um SELECT você tem que especificar em quais coluna você vai inserir os dados e quais colunas no SELECT você vai inserir, o número de colunas do INSERT deve ser o mesmo do SELECT, neste caso são 2 colunas em cada, o CONVERT(date, datacriacao) vai ser inserido na coluna1 e a colunaX será inserida na coluna2 Neste caso ele vai inserir na tabela2 as datas no formato YYYY-MM-DD porque você fez o SELECT convertendo para date.

Para tirar o ".000" você vai ter que converter o tipo de dados da coluna. Dá uma olhada nessa página (http://msdn.microsoft.com/en-us/library/ms186724.aspx), tem os formatos de data que o SQL Server suporta para colocar como tipo de dados em uma coluna. No seu caso o formato seria o smalldatetime, que deixaria a data como YYYY-MM-DD hh:mm:ss.

Abaixo o código para alteração:

ALTER TABLE	NOME_DA_TABELA
ALTER COLUMN	NOME_DA_COLUNA smalldatetime	NULL

[Erciley Junior]

Só por curiosidade, dá pra criar tabelas e fazer backups utilizando select também..

CREATE TABLE TESTE ENGINE = INNODB
SELECT * FROM tabela_clientes where codigo between 1 and 100;

Fazer isso não criará índices ou copiará o ENGINE, essas coisas devem ser especificadas manualmente.