Problemas para convertir de varchar a datetime

[dos_olivos]

Hola gente, ante nada gracias por la ayuda que me puedan aportar!
Soy novato en sql, y el tema basicamente es que necesito insertar datos que provienen de tipo de datos varchar en una columna que el tipo de dato sera datetime-
Ya de por si cuand hago el select me pincha:

select
convert(datetime,col_fecha,126) dia
from tabla


me tira este error:
Msg 241, Level 16, State 1, Line 3
Conversion failed when converting datetime from character string.

el dato desde el origen viene asi:20110103232219


Desde ya gracias a los aportes que puedan darme!

[iislas]

Lo que pasa que para SQL Server tu NUMEROTE, no es una fecha vaida, aunque para ti, tal vez, represente

2011-01-03
23:22:19

Debes "descomponer" tu numerote en algo comprensible para SQL Server, tal cual lo puse anteriormente, intentalo con SUBSTRING

[leonardo_josue]

Buenas tardes dos_olivos:

El error es bastante claro y se refiere a que la cadena que estás tratando de convertir no tiene un formato válido para su conversión. En tu post no mencionas qué formato de fecha tiene tu cadena, pero como estás tratando de convertirlo utilizando el estilo 126 puedo suponer que tienes un formato más o menos así yyyymmddhhmiss. Como te comenté en otro post el formato estándar que utiliza sql Server es el ANSI (yyyymmdd hh:mi:ss) por lo tanto la idea sería convertir tu cadena original a una que contenga este formato, sería más o menos así:

Código SQL:

DECLARE @fecha VARCHAR(29)
SET @fecha = '20110103232219'
SELECT CONVERT(datetime, SUBSTRING(@fecha, 1,8)+' '+ 
SUBSTRING(@fecha, 9,2)+':'+SUBSTRING(@fecha, 11,2)+':' + SUBSTRING(@fecha, 13,2)) AS fecha
 

el resultado de esta consulta sería

Código:

fecha
------------
2011-01-03 23:22:19.000

Saludos y espero que te sirva el código
Leo.

[iislas]

Precisamente a eso me referia, gracias Leonardo, le has hecho el trabajo a nuestro amig@

[dos_olivos]

Gracias a ambos--
Esa soluciòn es buena pero esto que les comentaba lo resolvi asi:
cast(stuff(stuff(left(t1.col_fecha,8),5,0,'-'),8,0,'-') as datetime) fecha

Si, ya se que parecere con esto medio cavernicola, pero ademas de la conversion lo tenia que agregar en un columna de tipo datetime en donde solo quieren ver la fecha -
Con esto consigo la fecha que corresponde y la hora seteada a 00:00:00.000-

Pero ahora tengo otra complicaciòn, siempre es insert select- Insertar en un campo datetime un select que proviene de un varchar. Este caso el dato vie algo asi:
23.01.2010 (viene carchar) y lo tengo que insertar en una columan datetime donde el formato es
2010-01-23 00:00:00.000 o algo asi se tiene qeu ver.

Probe esto pero no logro poder insertarla y me tira error-
cast(convert(nchar,stuff(stuff(RIGHT(t1.col_fecha, 4)+SUBSTRING(t1.col_fecha,4,2)+left(t1.col_fecha,2 ),5,0,'-'),8,0,'-'),103)as datetime) fecha

y esto otro
convert(datetime,convert(datetime, convert(varchar(10),t1.col_fecha103),103),102) fecha

pero no puedo.
Si a alguien se le ocurre sugerencia, seran bien venidas!

Gracias

[leonardo_josue]

Hola de nuevo dos_olivos dos cosas:

primero, para obtener sólo la fecha con la hora en cero, basta con que quites la concatenación de la hora en la respuesta que de dí, creo que te sirve perfectamente para lo que necesitas y sólo utilizas una función SUBSTRING en lugar de LEFT Y STUFF:

Código SQL:

DECLARE @fecha VARCHAR(30)
SET @fecha = '20110103232219'
SELECT CONVERT(DATETIME, SUBSTRING(@fecha, 1,8)) AS fecha
 

el resultado de esto sería:

Código:

fecha
-----------------
2011-01-03 00:00:00.000

Ahora bien, con respecto al segundo tema, no entendí muy bien qué es lo que necesitas, pero creo que tienes convertir una cadena "23.01.2010" a datetime, esto lo puedes hacer así:

Código SQL:

DECLARE @fecha VARCHAR(15)
SET @fecha = '23.01.2010'
SELECT CONVERT(DATETIME, REPLACE(@fecha, '.', '/'), 103) AS fecha
 

el resultado de esto sería:

Código:

fecha
-----------------
2011-01-23 00:00:00.000

Saludos
Leo

[dos_olivos]

Ante nada muchisimas gracias Leo-
La idea principal (siempre hablando de lo segundo), es convertirlo para para agregarlo en un campo datetime.
Probe el ejemplo suelto que me pasas y esta perfecto pero cuando lo aplico me arroja este otro error:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.

Como no es solo una cadena lo que quiero remplazar y son los datos de un campo lo hice de esta manera:
CONVERT(DATETIME, REPLACE(t1.col_fecha, '.', '/'), 103) AS fecha
Sera por eso que da el error?

Saludos

[leonardo_josue]

Este error está ocurriendo porque tus datos no siguen el formato dd.mm.yyyy que comentas, seguramente tienes algo como esto en alguno de los registros "01.13.2010". Esto es un error, pues no se puede asignar un mes 13 a una fecha.

Aquí depende de qué quieras hacer con los varchar que no sigan con el formato de fecha, tienes dos opciones, primero sería corregir aquellos registros erroneos o simplemente filtrar estos registros. Puedes hacer esto:

Código:

SET LANGUAGE Spanish
SELECT * FROM TuTabla T1 
WHERE ISDATE(REPLACE(T1.col_fecha, '.', '/')) = 0

esto te regresará todos los registros que no cumplan con el formato de fecha dd.mm.yyyy, para que hagas las modificaciones pertinentes.

Saludos
Leo.

[dos_olivos]

Sos un gros leo! Muchas gracias.
El problema estaba en que existian valores con 00.00.0000-
Como bien decias, no se puede asignar valores inexistentes o fuera del rango permitido por
el tipo de dato.
Asi que con la query que me pasastes vi que existian muchisimos valores por eso lo resolvi de esta manera:
(case isdate(T1.col_fecha) when 0 then '01.01.1900' else T1.col_fecha end)


Asi que te vuelvo agradecer !!

Saludos