Formato de fechas para DateTimePicker incompatible con SQLite

[martini002]

Hola Ecfisa,

Bueno no creo que sea muy conveniente usar el Reformat Date Strings.

Ideally the dates in the column would have two digits for the month, two for the day and 4 digits for the year, in which case it would be a simple matter of using the SUBSTR function to extract the elements and rearrange them as shown below.

Código SQL [-]
SUBSTR(date_field,7,4)||'-'||SUBSTR(date_field,1,2)||'-'||SUBSTR(date_field ,4,2)

Unfortunately in the example above, some of the values in the date column have only one digit for the month and or day. Using SELECT CASE and the CAST(substring AS INTEGER) function makes it possible to extract the value of the month and the day.

Código SQL [-]
(SELECT SUBSTR(date_field,-4,4)) ||'-'||(SELECT CASE WHEN CAST(SUBSTR(date_field,1,2) AS INTEGER)>=10 THEN SUBSTR(date_field,1,2) ELSE '0'|| CAST(SUBSTR (date_field,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(date_field,-7,3) AS INTEGER)>=10 THEN SUBSTR(date_field,-7,2) ELSE '0'|| CAST(SUBSTR(date_field,-6,2) AS INTEGER) END) AS 'date_field'

Código SQL [-]
 UPDATE table_name SET date_field = (select substr(date_field,-4,4)) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(date_field,1,2) AS INTEGER) >= 10 THEN SUBSTR(date_field,1,2) ELSE '0'|| CAST(SUBSTR (date_field,1,2) AS INTEGER) END ) ||'-'|| (SELECT CASE WHEN CAST(SUBSTR(date_field,-7,3) AS INTEGER)>=10 THEN SUBSTR(date_field,-7,2) ELSE '0'|| CAST(SUBSTR(date_field,-6,2) AS INTEGER) END);

NOTE: When doing a mass update in a table it is often a good idea to make a back up of the table before doing your changes.

Con respecto a lo otro voy a revisar sobre como colocar el formato a esto:

INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.

creo que podria ser lo mas adecuado sino se logra cambiar el formato de la fecha,
Saludos,
Gracias.

[martini002]

No he podido dar con un buen resultado aun,
si alguien mas tiene alguna idea para resolver esta incompatibilidad
entre el Picker y la BD le agradeceria muchisimo

Gracia