如何转换sqlite表中列的日期格式? [英] How can I convert the Date format for column in sqlite table?

问题描述

在sqlite中，我的表如下:

In sqlite, my table is as follows:

---------------------
Date           Temp
---------------------
201309010051    82
201309010151    81
201309010251    80
---------------------

对于日期"列，当前采用以下日期时间格式:YYYYMMDDHHMM.

For the 'Date column, it is currently in the following datetime format: YYYYMMDDHHMM.

我想改变整个日期列的这种格式，例如从:201309010051 到:
2013-09-01 00:51

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

第一行(即 201309010051)的错误/垃圾输出示例是:

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?

推荐答案

您正在错误地使用 substr() 函数.试试这个:

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

这里还有 substr 手册:https://www.techonthenet.com/sqlite/functions/substr.php

这篇关于如何转换sqlite表中列的日期格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！