In sqlite, my table is as follows:
---------------------
Date Temp
---------------------
201309010051 82
201309010151 81
201309010251 80
---------------------
For the 'Date column, it is currently in the following datetime format: YYYYMMDDHHMM.
I want to change this format of the entire date column, for example from : 201309010051 to :
2013-09-01 00:51
This is what I have tried so far:
select substr(Date, 7, 4)||"-"||substr(Date, 1,2)||"-"||substr(Date, 4,2) from myTable
An example of the incorrect/garbage output for the first row (i.e., 201309010051) is:
0100-20-30
How can achieve the desired format conversion?
You are using substr() function incorrectly. try this way:
select substr(Date, 1, 4)||"-"||substr(Date, 5, 2)||"-"||substr(Date, 7, 2)
||" "||substr(Date, 9, 2)||":"||substr(Date, 11, 2)
from myTable
also here is the substr manual: https://www.techonthenet.com/sqlite/functions/substr.php
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments