重新格式化列中的日期 [英] Reformat dates in column
问题描述
我在SQLite数据库中有以下格式的数据:
id column1 date
111280 1 / 1/2014
114275 1/2/2014
日期字段为TEXT类型。我已经知道了( https://www.sqlite.org/lang_datefunc.html ),我应该将日期格式设置为YYYY-MM-DD,以利用SQLite的datetime功能。是否有查询我可以运行以将格式更改为
mm / dd / yyyy
到
YYYY-MM-DD
到位?
您当前的日期格式有四种可能的形式:
m / d / yyyy
m / dd / yyyy
mm / d / yyyy
mm / dd / yyyy
要重新排列字段,请使用 substr()提取它们,然后将它们重新组合。 / p>
使用 instr(),但是对于一次性转换,只使用四个查询就更简单了:
更新MyTable
SET date = substr(date,6,4)|| ’-’||
substr(date,1,2)|| ’-’|| ‘0’||
substr(date,4,1)
WHERE date like‘__ / _ / ____’;
-这是mm / d / yyyy;类似地,对于其他形式,修改头寸并清零
I have some data in an SQLite DB of the form:
id column1 date
111 280 1/1/2014
114 275 1/2/2014
The date field is of type TEXT. I've been made aware (https://www.sqlite.org/lang_datefunc.html) that I should have the dates formatted like YYYY-MM-DD to take advantage of SQLite's datetime functionality. Is there a query I could run to change the format from
mm/dd/yyyy
to
YYYY-MM-DD
in place?
Your current date format has four possible forms:
m/d/yyyy
m/dd/yyyy
mm/d/yyyy
mm/dd/yyyy
To rearrange the fields, extract them with substr() and then combine them again.
It might be possible to determine the positions of the slashes with instr(), but for a one-off conversion, just using four queries is simpler:
UPDATE MyTable
SET date = substr(date, 6, 4) || '-' ||
substr(date, 1, 2) || '-' || '0' ||
substr(date, 4, 1)
WHERE date LIKE '__/_/____';
-- this is mm/d/yyyy; similarly for the other forms, modify positions and zeros
这篇关于重新格式化列中的日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!