CODEWITHSUNDEEP
sqlsqlite
user121
user121

Reputation: 931

How can I convert the Date format for column in sqlite table?

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?

Upvotes: 1

Views: 202

Answers (1)

Tedo G.
Tedo G.

Reputation: 1565

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

Upvotes: 1

Related Questions