SQL语句将字符串转换为date型

[一个写bug的小仙女]

将20151130221756 这种字符串转换为date型，并将年月日放入一个字段，时分秒放入另外一个字段；

[js14982]

to_date('20151130221756','yyyymmdd hh24:mi:ss') date格式就包含年月日时分秒了。你要分开可以用字符型（数值型）存储

[卖水果的net]

select to_char(to_date('20151130221756','yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') from dual 再按位截取，就查以了

[ghx287524027]

to_char,to_date,substr函数

[一个写bug的小仙女]

UPDATE DATECONVERT SET DATEYMR = (TO_DATE('20151130', YYYYMMDD)), TIMEHMS = (TO_DATE('221757', hh24:MM:SS)) where TIME='20151130221757' 自己的想法，应该to_date用的不对，麻烦各位大神帮忙改正

[ghx287524027]

UPDATE DATECONVERT
   SET DATEYMR = (TO_DATE(substr('20151130221757',1,8), ‘YYYYMMDD’)),
       TIMEHMS = (TO_DATE(substr('20151130221757',9,6), ’hh24:Mi:SS‘))
       where TIME='20151130221757'

[ghx287524027]

引用 7 楼 ghx287524027 的回复:

UPDATE DATECONVERT
   SET DATEYMR = (TO_DATE(substr('20151130221757',1,8), ‘YYYYMMDD’)),
       TIMEHMS = (TO_DATE(substr('20151130221757',9,6), ’hh24:Mi:SS‘))
       where TIME='20151130221757'

换成英文的逗号

UPDATE DATECONVERT
   SET DATEYMR = (TO_DATE(substr('20151130221757',1,8), 'YYYYMMDD')),
       TIMEHMS = (TO_DATE(substr('20151130221757',9,6), 'hh24:Mi:SS'))
       where TIME='20151130221757'

[Z_B_Hrunqian]

引用 4 楼 qq_34516352 的回复:

UPDATE DATECONVERT SET DATEYMR = (TO_DATE('20151130', YYYYMMDD)), TIMEHMS = (TO_DATE('221757', hh24:MM:SS)) where TIME='20151130221757' 自己的想法，应该to_date用的不对，麻烦各位大神帮忙改正

你把时分秒的按年月日时分秒中去截取

[宋哥]

引用 4 楼 qq_34516352 的回复:

UPDATE DATECONVERT SET DATEYMR = (TO_DATE('20151130', YYYYMMDD)), TIMEHMS = (TO_DATE('221757', hh24:MM:SS)) where TIME='20151130221757' 自己的想法，应该to_date用的不对，麻烦各位大神帮忙改正

格式要加引号，分钟是MI，不是MM：UPDATE DATECONVERT SET DATEYMR = (TO_DATE('20151130', ‘YYYYMMDD’)), TIMEHMS = (TO_DATE('221757', ‘hh24:MI:SS’)) where TIME='20151130221757'