How to pre-load a date value on a form

[Ajotah98]

Hi!

I'm trying to create a form to edit data.
In the table that I manage to store the data, there's a date type column:

CREATE TABLE IF NOT EXISTS TICKETS (
    TICKETID INTEGER PRIMARY KEY AUTOINCREMENT,
    FECHA_TICKET DATE NOT NULL DEFAULT (datetime('now','localtime')),
    DELEGACION TEXT NOT NULL,
    PRODUCTO TEXT NOT NULL DEFAULT 'MODULAB',
    CLIENTE INTEGER NOT NULL,
    DESCRIPCION TEXT,
    COMENTARIOS TEXT,
    KAM INTEGER NOT NULL,
    ITOPS_SPECIALIST TEXT NOT NULL,
    PEDIDO INTEGER NOT NULL,
    ESTADO_SAP INTEGER NOT NULL,
    ESTADO_ITOPS INTEGER NOT NULL,
    FOREIGN KEY (DELEGACION) REFERENCES DELEGACIONES(CODIGO_DELEGACION),
    FOREIGN KEY (CLIENTE) REFERENCES CLIENTES(CLIENTEID),
    FOREIGN KEY (KAM) REFERENCES KAMS(KAMID),
    FOREIGN KEY (ITOPS_SPECIALIST) REFERENCES ITOPS(NOMBRE_ITOPS),
    FOREIGN KEY (ESTADO_SAP) REFERENCES ESTADOS_SAP(ESTADO_SAPID),
    FOREIGN KEY (ESTADO_ITOPS) REFERENCES ESTADOS_ITOPS(ESTADO_ITOPSID)
);

The "FECHA_TICKET" (ticket date in spanish) column is one of the columns that I want to preload into the form.

Here is the actual query:

select
    'form' as component,
    'Crear o modificar ticket' as title,
    'Guardar cambios' as validate,
    'gray-50' as validate_color,
    'blue' as validate_outline,
    case 
        when  $ticket is not null then 'modify_ticket.sql' 
        else 'create_ticket.sql'
    end as action;
SELECT
    'Fecha de creación' AS name,
    'text' AS type,
    FECHA_TICKET AS value
FROM TICKETS
WHERE TICKETID = $ticket;

And here is the result:

But, this happens when I change the type of the row:

SELECT
    'Fecha de creación' AS name,
    'date' AS type, -- instead of 'text'
    FECHA_TICKET AS value
FROM TICKETS
WHERE TICKETID = $ticket;

The date doesn't load into the field.

How can I manage to load the date into the date picker?

[Ajotah98]

I solved it...

The problem was (and is) that I store the dates on a bad format (DD/MM/YYYY), and when I try to load it, well it doesn't load.

I converted the date as shown here:

SELECT
    'Fecha de creación' AS label,
    'date' AS type, 
    'fechaticket' as name,
    SUBSTR(FECHA_TICKET, 7, 4) || '-' || 
    SUBSTR(FECHA_TICKET, 4, 2) || '-' || 
    SUBSTR(FECHA_TICKET, 1, 2) AS value -- Chapuza porque guardamos los valores en el formato DD/MM/YYYY y para que SQLite3 pueda procesarlo con sus funciones nativas tiene que estar en YYYY-MM-DD HH:MM:SS o similar.
FROM TICKETS
WHERE TICKETID = $ticket;

[lovasoa]

Hello @Ajotah98 !

I'm glad you found a solution to your problem !

However, I'd still advise you to store dates either in YYYY-MM-DD format, or even as timestamps (numbers) in your database, because these are the native formats that SQLite understands, and that you will be able to use with SQLite date and time functions:
https://www.sqlite.org/lang_datefunc.html

Illustration of the problem

CREATE TABLE IF NOT EXISTS TICKETS (
    TICKETID INTEGER PRIMARY KEY AUTOINCREMENT,
    FECHA_TICKET DATE NOT NULL DEFAULT (datetime('now','localtime')));

INSERT INTO TICKETS(FECHA_TICKET) VALUES
  ('2024-03-01'),
  ('01/03/2024');


select FECHA_TICKET, strftime('%d %m', FECHA_TICKET) from TICKETS;

-- 2024-03-01,"01 03"
-- 01/03/2024,NULL

[lovasoa]

You can easily go from a timestamp to whatever date format you like, but the reverse is not true.

For instance, you can easily store your data compactly as numbers, and then format them as DD/MM/YYYY if you want.

SELECT strftime('%s');
-- 1709296870

SELECT strftime('%d/%m/%Y', 1709296739, 'unixepoch');
-- 01/03/2024