Fecha desde campo Varchar a campo datetime con insert

[ejconan]

Buenas,

Teno una tabla con campos varchar que he creado a partir de un TXT.

Luego tengo que mover los datos de esa tabla a otra, dandoles formato a los numeros y fechas, pues bien, las fechas están en formato 25-05-2009 10:50

no logro pasarlo a la nueva tabla de ninguna manera, he probado asi:
(en la consulta hay mas campos, esto es un ejemplo)

Insert into TBLNUEVA (CAMPOFECHA2)
SELECT (DATE_FORMAT('CAMPOFECHA1', '%Y-%m-&d') )FROM TBLVIEJA;

Insert into TBLNUEVA (CAMPOFECHA2)
SELECT (LEFT(CAMPOFECHA1,10 ) )FROM TBLVIEJA;

Insert into TBLNUEVA (CAMPOFECHA2)
SELECT (STR_TO_DATE('CAMPOFECHA1', '%Y-%m-&d') )FROM TBLVIEJA;

Alguna idea?

[Genetix]

Hola
Prueba esto

Código sql:

INSERT INTO TBLNUEVA (CAMPOFECHA2)
SELECT (STR_TO_DATE('CAMPOFECHA1', '%d-%m-%Y') )FROM TBLVIEJA;
 

[ejconan]

Que diferencia hay entre

Insert into TBLNUEVA (CAMPOFECHA2)
SELECT (STR_TO_DATE('CAMPOFECHA1', '%Y-%m-&d') )FROM TBLVIEJA;

y esto que me pones

INSERT INTO TBLNUEVA (CAMPOFECHA2)
SELECT (STR_TO_DATE('CAMPOFECHA1', '%Y-%m-&d') )FROM TBLVIEJA;

Solo veo el &d de la fecha

Voy a probar
Salu2

[ejconan]

Pues nada, sigue con la misma:

Incorrect datetime value: 'FX_PRESENCIAL' for function str_to_date


Me estoy liando... se me pegan los ojos ya,

juraria que hace un momento habia visto otra cosa y no esta: '%d-%m-%Y' en tu post... jejeje

[ejconan]

Nada no hay forma... ni el str_to_date y el date_format

no se que hago mal

[huesos52]

En que formato tienes las fechas en el campo varchar?

Puedes poner algunos registros de tus tablas?
cual es el error?
Que operación te ejecuta mal?

un saludo.

[ejconan]

El formato del dato en el campo varchar es:

2009-05-25 19:00

Y-m-d H:m

Cuando hago el insert me dice que valor incorrecto de fecha

[huesos52]

Prueba esto:

Código mysql:

INSERT INTO TBLNUEVA (CAMPOFECHA2)
SELECT (str_to_date(CAMPOFECHA1, '%Y-%m-&d %H:%i') )FROM TBLVIEJA;
 

o

Código mysql:

INSERT INTO TBLNUEVA (CAMPOFECHA2)
SELECT CAMPOFECHA1 FROM TBLVIEJA;
 

Nos cuentas como te va.

[ejconan]

Ya he hecho las pruebas, este es el resultado:


INSERT INTO TBLNUEVA (CAMPOFECHA2)SELECT (STR_TO_DATE(CAMPOFECHA1, '%Y-%m-&d %H:%i') )FROM TBLVIEJA;

Mensaje d eerror: Incorrect datetime value: 'CAMPOFECHA1' for function str_to_date


INSERT INTO TBLNUEVA (CAMPOFECHA2)SELECT CAMPOFECHA1 FROM TBLVIEJA;

Mensaje de error: Incorrect date value: '20/02/2001 19:00:00' for column 'CAMPOFECHA1'


Hay que tener en cuenta que en la TBLVIEJA el CAMPOFECHA1 es de tipo VARCHAR

[huesos52]

La verdad no creo que tu formato sea Y-m-d H:i

Mira el error que te saca:

Mensaje de error: Incorrect date value: '20/02/2001 19:00:00' for column 'CAMPOFECHA1'

haz un:
desc tblvieja;
y un
select campofecha1 from tblvieja limit 5;
y un
desc tblnueva;

Y pegalos aca.

Con esa información te podemos ayudar.

Un saludo.

[ejconan]

Bueno pues aqui teneis, la tabla con la que estoy trabajando, lo anterior eran ejemplos:

************************************************** *ESTA ES LA TABLA DE ORIGENL (TBLVIEJA EN EL EJEMPLO ANTERIOR)
ESTA TABLA ES TODO VARCHAR POR QUE LO HE IMPORTADO DE UN TXT CON LOAD_DATA_INFILE

mysql> desc tablatemporal;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| NU_BOLETIN | varchar(50) | YES | | NULL | |
| USUARIO_PEDIDO | varchar(50) | YES | | NULL | |
| REFERENCIA_PEDIDO | varchar(50) | YES | | NULL | |
| PRESENCIAL | varchar(50) | YES | | NULL | |
| FX_PRESENCIAL | varchar(50) | YES | | NULL | |
| NO_PERMANENCIA | varchar(50) | YES | | NULL | |
| ESTADO_SOLUCION | varchar(50) | YES | | NULL | |
| N_LINEA | varchar(50) | YES | | NULL | |
| CO_GRUPO | varchar(50) | YES | | NULL | |
| NO_GRUPO | varchar(50) | YES | | NULL | |
| CO_EMPRESA | varchar(50) | YES | | NULL | |
| NO_EMPRESA | varchar(50) | YES | | NULL | |
| CO_TIENDA | varchar(50) | YES | | NULL | |
| NO_TIENDA | varchar(50) | YES | | NULL | |
| CO_MATRICULA | varchar(50) | YES | | NULL | |
| DS_PERFIL | varchar(50) | YES | | NULL | |
| FX_ALTA_BOLETIN | varchar(50) | YES | | NULL | |
| CO_TIPO_BOL | varchar(50) | YES | | NULL | |
| ESTADO_TQ_BOL | varchar(50) | YES | | NULL | |
| FX_ESTADO_TQ_BOL | varchar(50) | YES | | NULL | |
| ESTADO_HARPAGONDC_BOL | varchar(50) | YES | | NULL | |
| FX_ESTADO_HARPAGON_DC_BOL | varchar(50) | YES | | NULL | |
| ESTADO_LIQUIDACION | varchar(50) | YES | | NULL | |
| FX_ESTADO_LIQUIDACION | varchar(50) | YES | | NULL | |
| CO_LIQUIDACION | varchar(50) | YES | | NULL | |
| FX_LIQUIDACION | varchar(50) | YES | | NULL | |
| CA_COMISION_INICIAL_BOL | varchar(50) | YES | | NULL | |
| CA_COMISION_FINAL_BOL | varchar(50) | YES | | NULL | |
| NU_ABONO | varchar(50) | YES | | NULL | |
| NO_CLIENTE | varchar(100) | YES | | NULL | |
| CO_TIPO_DOC | varchar(50) | YES | | NULL | |
| NU_DOC_CLIENTE | varchar(50) | YES | | NULL | |
| NO_PROVINCIA_INSTALACION | varchar(50) | YES | | NULL | |
| CO_TERRITORIO | varchar(50) | YES | | NULL | |
| CO_SEGMENTO | varchar(50) | YES | | NULL | |
| CO_SUBSEGMENTO | varchar(50) | YES | | NULL | |
| CO_AGRUPACION_DC | varchar(50) | YES | | NULL | |
| NO_AGRUPACION_DC | varchar(100) | YES | | NULL | |
| IN_ESTADO_AGRUPACION_DC | varchar(50) | YES | | NULL | |
| IN_OPERACION | varchar(50) | YES | | NULL | |
| CA_UNIDADES | varchar(50) | YES | | NULL | |
| CA_VALORACION_LINEA_INICIAL | varchar(50) | YES | | NULL | |
| CA_VALORACION_LINEA_FINAL | varchar(50) | YES | | NULL | |
| CA_VALORACION_AGRU_DC | varchar(50) | YES | | NULL | |
| CA_COMISION_LINEA_INICIAL | varchar(50) | YES | | NULL | |
| CA_COMISION_LINEA_FINAL | varchar(50) | YES | | NULL | |
| IN_SENIALIZADO | varchar(50) | YES | | NULL | |
| CA_COMISION_PREVISTA | varchar(50) | YES | | NULL | |
| CO_REGULARIZACION | varchar(50) | YES | | NULL | |
| FX_REGULARIZACION | varchar(50) | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
50 rows in set (0.00 sec)


********************************************* ESTE ES EL CAMPO DEL PROBLEMA, AL IGUAL QUE TODOS LOS QUE EMPIEZAN POR FX

mysql> select fx_presencial from tablatemporal limit 5;
+---------------------+
| fx_presencial |
+---------------------+
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
+---------------------+
5 rows in set (0.00 sec)



************************************************** *ESTA ES LA TABLA DE DESTINO (TBLNUEVA EN EL EJEMPLO ANTERIOR)

mysql> desc tblfiltro;
+-----------------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------------+--------------+------+-----+---------+-------+
| BOLETIN | int(10) | NO | MUL | NULL | |
| PRESENCIAL | varchar(50) | YES | | NULL | |
| FX_PRESENCIAL | date | YES | | NULL | |
| NO_PERMANENCIA | varchar(100) | YES | | NULL | |
| CO_EMPRESA | int(10) | NO | | NULL | |
| CO_TIENDA | int(10) | NO | | NULL | |
| FX_ALTA | date | YES | | NULL | |
| CO_TIPO_BOL | varchar(4) | YES | | NULL | |
| ESTADO_TQ_BOL | varchar(4) | YES | | NULL | |
| FX_ESTADO_TQ_BOL | date | YES | | NULL | |
| ESTADO_HARPAGONDC_BOL | varchar(4) | YES | | NULL | |
| FX_ESTHARP | date | YES | | NULL | |
| ESTADO_LIQUIDACION | varchar(20) | YES | | NULL | |
| FX_ESTADO_LIQUIDACION | date | YES | | NULL | |
| CO_LIQUIDACION | int(10) | YES | | NULL | |
| FX_LIQUIDACION | date | YES | | NULL | |
| CA_COMISION_INICIAL_BOL | double(15,5) | YES | | NULL | |
| CA_COMISION_FINAL_BOL | double(15,5) | YES | | NULL | |
| NU_ABONO | varchar(20) | YES | | NULL | |
| NO_CLIENTE | varchar(150) | YES | | NULL | |
| NU_DOC_CLIENTE | varchar(50) | YES | | NULL | |
| NO_PROVINCIA_INSTALACION | varchar(50) | YES | | NULL | |
| CO_TERRITORIO | varchar(4) | YES | | NULL | |
| CO_SEGMENTO | varchar(4) | YES | | NULL | |
| CO_SUBSEGMENTO | varchar(4) | YES | | NULL | |
| CO_AGRUPACION_DC | int(20) | YES | | NULL | |
| NO_AGRUPACION_DC | varchar(100) | YES | | NULL | |
| IN_ESTADO_AGRUPACION_DC | varchar(4) | YES | | NULL | |
| IN_OPERACION | varchar(4) | YES | | NULL | |
| CA_UNIDADES | int(10) | YES | | NULL | |
| CA_VALORACION_LINEA_INICIAL | double(15,5) | YES | | NULL | |
| CA_VALORACION_LINEA_FINAL | double(15,5) | YES | | NULL | |
| CA_VALORACION_AGRU_DC | double(15,5) | YES | | NULL | |
| CA_COMISION_LINEA_INICIAL | double(15,5) | YES | | NULL | |
| CA_COMISION_LINEA_FINAL | double(15,5) | YES | | NULL | |
| IN_SENIALIZADO | int(4) | YES | | NULL | |
| CA_COMISION_PREVISTA | double(15,5) | YES | | NULL | |
| CO_REGULARIZACION | int(10) | YES | | NULL | |
| FX_REGULARIZACION | date | YES | | NULL | |
+-----------------------------+--------------+------+-----+---------+-------+
39 rows in set (0.02 sec)

[gnzsoloyo]

Lo que puede que esté resultando mal es que el formato de conversión se refiere no a como tiene que quedar sino a cómo está entrando.
Sea el caso :
Si el formato de entrada es "dd/mm/yyyy HH:MM:ss", entonces lo que hay que indicar es que ese es el formato de ingreso:

Código sql:

SELECT STR_TO_DATE('20/02/2001 19:00:00','%d/%m/%Y %T') FECHA;
 

En el caso en que las horas lleguen sin segundos:

Código sql:

SELECT STR_TO_DATE('20/02/2001 19:20','%d/%m/%Y %H:%i') FECHA;
 

En ningún caso se debe olvidar poner los caracteres y/o espacios que separan los números, porque sino devolverá NULL.

Entonces, si el valor está entrando así:

'20/02/2001 19:20:00'

Se debe poner:

Código sql:

INSERT INTO TblNueva (CAMPOFECHA2) SELECT (STR_TO_DATE(CAMPOFECHA1, '%d/%m/%Y %H:%i:%s') )
FROM TblVieja;
 

No perder de perspectiva que lo que hace STR_TO_DATE() es devolver un DATETIME no una cadena, en formato de 8 bytes, por lo que se manipula a partir de allí como un valor DATETIME común...

[ejconan]

entonces si primero hago el STR_TO_DATE, transformo la cadena en DATETIME, ya solo queda cambiar el formato del datetime

algo así valdría?:

DATE_FORMAT (STR_TO_DATE('CAMPOFECHA1','%d/%m/%Y %H:%m'))



Salu2

[huesos52]

Haz exactamente como dice gnzsoloyo.

En pocas palabras, cuando haces un str_to_date, lo que haces, es permitir la entrada de un formato no recibido por mysql, pero transformado internamente por la función str_to_date para ingresarlo como se debe.

INSERT INTO TblNueva (CAMPOFECHA2) SELECT (STR_TO_DATE(CAMPOFECHA1, '%d/%m/%Y %H:%i:%s') )
FROM TblVieja;

Así debe quedar.

Al hacer esto, internamente será insertada la fecha en formato Y-m-d HH:MM:ss

Un saludo.

[gnzsoloyo]

Iniciado por ejconan

entonces si primero hago el STR_TO_DATE, transformo la cadena en DATETIME, ya solo queda cambiar el formato del datetime

algo así valdría?:

DATE_FORMAT (STR_TO_DATE('CAMPOFECHA1','%d/%m/%Y %H:%m'))



Salu2

Si vas a ingresar el dato en un campo DATETIME, y STR_TO_DATE() te devuelve un DATETIME, ¿para qué quieres cambiarle el formato? No tiene ninguna utilidad. El valor ya es el que el campo debe recibir...

Ahora bien, si lo quieres guardar en un campo CHAR o VARCHAR, si tendría "algo" de sentido.

[ejconan]

Lo decía porque lo que me devuelve el str_to_date es en formato d/m/Y y mysql utiliza Y/m/d

Ahora pruebo a ver y os cuento

[gnzsoloyo]

Iniciado por ejconan

Lo decía porque lo que me devuelve el str_to_date es en formato d/m/Y y mysql utiliza Y/m/d

Ahora pruebo a ver y os cuento

Cuando se ejecuta STR_TO_DATE ya queda en formato "yyyy-MM-dd HH:mm:ss", porque está en el formato interno de DATETIME en MySQL.
Eso es lo que te estamos aclarando. No confundas la cadena con el DATETIME...

[huesos52]

Otra cosa ejconan...
Deberías considerar cambiar el tipo de dato del atributo FX_PRESENCIAL de date a datetime.
Aunque no te genere error, solo guardará la fecha y no la hora como lo necesitas.

Un saludo.

[ejconan]

Wenas,

Me sigue dando error: Incorrect datetime value: 'FX_PRESENCIAL' for function str_to_date


El unico dato que necesito es la fecha, la hora no me vale para nada. Luego seguiré que ahora me tengo que ir.

Lo haré nuevo detenidamente y os cuento vale??

Muchisimas gracias

[huesos52]

Mira te dejo todo el procedimiento con tu ejemplo.
Espero te sirva.

Código mysql:

mysql> create table tablatemporal(fx_presencial varchar(50));
Query OK, 0 rows affected (0.02 sec)
 
mysql> insert into tablatemporal values('20/02/2001 19:00:00'
    -> );
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tablatemporal values('20/02/2001 19:00:00');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tablatemporal values('20/02/2001 19:00:00');
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into tablatemporal values('20/02/2001 19:00:00');
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into tablatemporal values('20/02/2001 19:00:00');
Query OK, 1 row affected (0.00 sec)
 
mysql> select *from tablatemporal;
+---------------------+
| fx_presencial       |
+---------------------+
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
| 20/02/2001 19:00:00 |
+---------------------+
5 rows in set (0.00 sec)
 
mysql> create table tblfiltro(fx_presencial date);
Query OK, 0 rows affected (0.00 sec)
 
mysql> alter table tblfiltro modify fx_presencial datetime;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> insert into tblfiltro select str_to_date(fx_presencial,'%d/%m/%Y %H:%i:%s') from tablatemporal;
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0
 
mysql> select *from tblfiltro;
+---------------------+
| fx_presencial       |
+---------------------+
| 2001-02-20 19:00:00 |
| 2001-02-20 19:00:00 |
| 2001-02-20 19:00:00 |
| 2001-02-20 19:00:00 |
| 2001-02-20 19:00:00 |
+---------------------+
5 rows in set (0.00 sec)
 
mysql>
 

Con el alter table modifico el tipo de campo de date a datetime.

Creo que es lo que necesitas.

[ejconan]

joer huesos52 eres una maquina, gracias por tomarte tantas molestias, ya lo he hecho y sigue diciendo incorrect datetime value, algo mal tengo por ahí, voy a revisar todo de nuevo

Salu2

[gnzsoloyo]

Iniciado por ejconan

Wenas,

Me sigue dando error: Incorrect datetime value: 'FX_PRESENCIAL' for function str_to_date


El unico dato que necesito es la fecha, la hora no me vale para nada. Luego seguiré que ahora me tengo que ir.

Lo haré nuevo detenidamente y os cuento vale??

Muchisimas gracias

Como tienes el mismo nombre de campo en ambas tablas, es un poco difícil saber en cuál se produce el problema. De todos modos, esto:

Incorrect datetime value: 'FX_PRESENCIAL' for function str_to_date

parece indicar que hay un problema en el dato entrante a la función STR_TO_DATE, no a la tabla destino.
Si fuese la tabla destino, el que sea un DATE y no un DATETIME es irrelevante, porque MySQL "corta" el dato al formato correcto, esto es, suprime la hora.
Yo primero verificaría si en la tabla origen todos los strings tienen el formato correcto.
Para mas seguridad postea la sitaxis que estas usando. Es decir. COPIA lo que estás usando, no la transcribas (las transcripciones suelen esconder los errores).

[ejconan]

Funciona!!!

gracias a todos, ahora solo tengo que hacer lo mismo con las otras 4 o 5 campos de fechas y luego con los campos integer y con los double.

una cosa, cuando el campo FX_Presencial de la tabla de origen sea NULL que pasa?

voy a probar

[gnzsoloyo]

La aritmética de SQL es muy precisa: Toda operación aritmética, de cadena o relacional con NULL es igual a NULL. SIEMPRE.
Esto quiere decir que no importa cómo lo combines, cuando un campo sumado, multiplicado, dividido, restado o encadenado tiene NULL, el resultado es NULL.

[ejconan]

Bueno pues las fechas ya están solucionadas, ahora los campos de numeros

Muchas gracias por la ayuda, voy a investigar sobre los numeros, si tengo alguna duda ya os preguntaré pero quiero sacarlo yo solito :)

[ejconan]

Ale ya estamos liaos, los campos double van bien, pero tengo otro problema:

Campo CO_LIQUIDACION en tablatemporal es VARCHAR y está vacío
Le hago el insert a tblfiltro que es INTEGER y me dice que valor incorrecto,

¿no habiamos dicho que los null eran iguales para todos?

[huesos52]

ejconan no confundas un valor null con un vacío.
Son dos cosas totalmente diferentes.

Si tienes valores vacíos yo los pasaría a null y posteriormente haría la operación.

Código mysql:

update tabla set campo = null where campo = '';
 

Pruebalo y nos cuentas.

[ejconan]

si huesos52, es que me he liado al escribir,

verás como la tablatemporal la genero con el loaddatainfile pues los valores vacios me les deja vacios, no como null, estoy buscando alguna propiedad de load data in file para que los vacios me los grabe como null y asi hacerlo todo de un plumazo, he encontrado esto:

WRITE EMPTY PLACEHOLDERS AS [EMPTY | NULL]

pero no funciona

seguiré buscando, sino pues hago el update que es lo que hice antes para probar

Salu2

[ejconan]

Buenas otra vez,

Nada, no encuentro mas documentacion sobre el Loaddatainfile, y eso que puse ayer (WRITE EMPTY PLACEHOLDERS AS [EMPTY | NULL]) no funciona, asi que lo haré con un update como dijimos ayer, aunque tenga que hacer 4 o 5 consultas mas, creo que no relentizaré mucho el proceso. Es que esto que estoy haciendo cuando termina todo lo que tiene que hacer habrá ejecutado unas 20 sqls diferentes, por eso pienso que cuanto menos mejor ¿no?

También tengo que mirar como hago un control de errores en asp, pero eso es otra historia