How to format string into date sqlite

IM working with a table that has a date format as m/d/Y Hr:Min:Sec. How can I cast this field into a format such as Y-m-d. The table b1 looks as:

        date                    place           app
        07/18/2020 15:14:37     Search          0
        03/05/2021 16:15:18     Search          0
        07/02/2020 18:18:00     Search          0
        06/12/2020 16:56:51     Search          0

I'm going to group by place and date, but I need to create a date without the time. The cast datatype can be a string. How can I do such transformation

The table b1 should look as:

        date            place           app
        2020-07-18      Search          0
        2021-03-05      Search          0
        2020-07-02      Search          0
        2020-06-12      Search          0

    

1 answer

  • answered 2021-11-23 01:46 Tim Biegeleisen

    Sadly SQLite is pretty thin when it comes to in house date support. You may use SUBSTR here along with substring operations:

    SELECT
        SUBSTR(date, 7, 4) || '-' || SUBSTR(date, 1, 2) || '-' ||
        SUBSTR(date, 4, 2) AS date,
        place,
        app
    FROM yourTable;
    

How many English words
do you know?
Test your English vocabulary size, and measure
how many words do you know
Online Test
Powered by Examplum