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
>Solution :
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;