How to format string into date sqlite-CodePudding

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

CodePudding user response：

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;