sqlite 日期字符串转integer

 update lg

-- 更新数据

SET age = CAST((

date(

-- 格式化 jz_sj (取前10位日期部分进行处理)

printf('%04d-%02d-%02d',

substr(replace(jz_sj, '/', '-'), 1, 4),

CAST(substr(replace(jz_sj, '/', '-'), 6, instr(substr(replace(jz_sj, '/', '-'), 6), '-') - 1) AS INTEGER),

CAST(substr(replace(jz_sj, '/', '-'), 6 + instr(substr(replace(jz_sj, '/', '-'), 6), '-')) AS INTEGER)

)

) -

julianday(

-- 格式化 csrq

printf('%04d-%02d-%02d',

substr(replace(csrq, '/', '-'), 1, 4),

CAST(substr(replace(csrq, '/', '-'), 6, instr(substr(replace(csrq, '/', '-'), 6), '-') - 1) AS INTEGER),

CAST(substr(replace(csrq, '/', '-'), 6 + instr(substr(replace(csrq, '/', '-'), 6), '-')) AS INTEGER)

)

)

) / 365.25 AS INTEGER)

WHERE csrq IS NOT NULL AND jz_sj IS NOT NULL;

-- To add a new column `csrq2` to the `lg` table and populate it by converting the `CSRQ` string to a date format, you can use the following SQL statements. Note that SQLite does not have a dedicated date type, so dates are typically stored as text in the format 'YYYY-MM-DD'.

--

-- First, add the new column:

ALTER TABLE lg ADD COLUMN csrq2 integer;

-- Then, update the `csrq2` column with the formatted date:

UPDATE lg

SET csrq2 = date(

printf('%04d-%02d-%02d',

substr(replace(CSRQ, '/', '-'), 1, 4),

CAST(substr(replace(CSRQ, '/', '-'), 6, instr(substr(replace(CSRQ, '/', '-'), 6), '-') - 1) AS INTEGER),

CAST(substr(replace(CSRQ, '/', '-'), 6 + instr(substr(replace(CSRQ, '/', '-'), 6), '-')) AS INTEGER)

)

);

-- This will convert the `CSRQ` string to a date format and store it in the `csrq2` column.

set csrq2=date( -- 格式化 csrq

printf('%04d-%02d-%02d',

substr(replace(csrq, '/', '-'), 1, 4),

CAST(substr(replace(csrq, '/', '-'), 6, instr(substr(replace(csrq, '/', '-'), 6), '-') - 1) AS INTEGER),

CAST(substr(replace(csrq, '/', '-'), 6 + instr(substr(replace(csrq, '/', '-'), 6), '-')) AS INTEGER)

))

SELECT

CSRQ as 原始,

date(

substr(CSRQ, 1, 4) || '-' ||

printf('%02d', CAST(substr(CSRQ, 6, instr(substr(CSRQ, 6), '/') - 1) AS INTEGER)) || '-' ||

printf('%02d', CAST(substr(substr(CSRQ, 6), instr(substr(CSRQ, 6), '/') + 1) AS INTEGER))

) as 标准日期

FROM lg

LIMIT 10;

SELECT

JZ_SJ as 原始,

datetime(

substr(JZ_SJ, 1, 4) || '-' ||

printf('%02d', CAST(substr(JZ_SJ, 6, instr(substr(JZ_SJ, 6), '/') - 1) AS INTEGER)) || '-' ||

printf('%02d', CAST(trim(substr(JZ_SJ, instr(JZ_SJ, '/') + 1 + instr(substr(JZ_SJ, instr(JZ_SJ, '/') + 1), '/')), ' ') AS INTEGER)) ||

' ' ||

substr(JZ_SJ, instr(JZ_SJ, ' ') + 1)

) as 标准时间

FROM lg;

select jz_sj from lg limit 10;

pragma table_info(lg)