如何把access中的yyyymmdd字符串，转化为yyyymmdd 用sql实现。谢谢了

假设表名为table1,字串 yyyymmdd字段名为col1 转换语句如下

update table1 set col1=left(col1,4) & "/" & mid(col1,5,2) & "/" & right(col1,2)

注意，更改前该日期字段中的日期格式必须严格符合yyyymmdd的格式规范。否则无法正确完成转换。也可以不update改为直接select ,转换表达式是一样的

可以尝试用以下语句来转换：(前提是ODATE字段是日期型date）

select to_char(ODATE,'YYYY/MM/DD') odate from 表名 where rownum=1;

如果ODATE是字符型的话，还要再转换一道：

select to_char(to_date(ODATE,'DD-MM-YYYY'),'YYYY/MM/DD') odate from 表名 where rownum=1;

用convert函数，

如：convert(varchar(20),字段名,120)这样子，但是我查了一下，没有全部都连接在一起的参数

>

1右击要转换格式的单元格，选择“设置单元格格式”，2在对话框中选择“数字”选项卡，并在分类中选择最后一个“自定义”

3在右侧“类型”中输入“yyyymmdd”后按确定即可

1> select Convert(varchar(8), GETDATE(), 112)

2> go

--------

20110901

(1 行受影响)

如果你那里执行的结果是 2011-09-01, 就比较奇怪了

你执行一下 下面的 SQL， 看看 哪种结果合适

SELECT 0 AS ID , Convert(VARCHAR(30), GETDATE(), 0 ) AS result UNION ALL

SELECT 100 AS ID , Convert(VARCHAR(30), GETDATE(), 100) AS result UNION ALL

SELECT 1 AS ID , Convert(VARCHAR(30), GETDATE(), 1 ) AS result UNION ALL

SELECT 101 AS ID , Convert(VARCHAR(30), GETDATE(), 101) AS result UNION ALL

SELECT 2 AS ID , Convert(VARCHAR(30), GETDATE(), 2 ) AS result UNION ALL

SELECT 102 AS ID , Convert(VARCHAR(30), GETDATE(), 102) AS result UNION ALL

SELECT 2 AS ID , Convert(VARCHAR(30), GETDATE(), 2 ) AS result UNION ALL

SELECT 102 AS ID , Convert(VARCHAR(30), GETDATE(), 102) AS result UNION ALL

SELECT 3 AS ID , Convert(VARCHAR(30), GETDATE(), 3 ) AS result UNION ALL

SELECT 103 AS ID , Convert(VARCHAR(30), GETDATE(), 103) AS result UNION ALL

SELECT 4 AS ID , Convert(VARCHAR(30), GETDATE(), 4 ) AS result UNION ALL

SELECT 104 AS ID , Convert(VARCHAR(30), GETDATE(), 104) AS result UNION ALL

SELECT 5 AS ID , Convert(VARCHAR(30), GETDATE(), 5 ) AS result UNION ALL

SELECT 105 AS ID , Convert(VARCHAR(30), GETDATE(), 105) AS result UNION ALL

SELECT 6 AS ID , Convert(VARCHAR(30), GETDATE(), 6 ) AS result UNION ALL

SELECT 106 AS ID , Convert(VARCHAR(30), GETDATE(), 106) AS result UNION ALL

SELECT 7 AS ID , Convert(VARCHAR(30), GETDATE(), 7 ) AS result UNION ALL

SELECT 107 AS ID , Convert(VARCHAR(30), GETDATE(), 107) AS result UNION ALL

SELECT 8 AS ID , Convert(VARCHAR(30), GETDATE(), 8 ) AS result UNION ALL

SELECT 108 AS ID , Convert(VARCHAR(30), GETDATE(), 108) AS result UNION ALL

SELECT 9 AS ID , Convert(VARCHAR(30), GETDATE(), 9 ) AS result UNION ALL

SELECT 109 AS ID , Convert(VARCHAR(30), GETDATE(), 109) AS result UNION ALL

SELECT 10 AS ID , Convert(VARCHAR(30), GETDATE(), 10 ) AS result UNION ALL

SELECT 110 AS ID , Convert(VARCHAR(30), GETDATE(), 110) AS result UNION ALL

SELECT 11 AS ID , Convert(VARCHAR(30), GETDATE(), 11 ) AS result UNION ALL

SELECT 111 AS ID , Convert(VARCHAR(30), GETDATE(), 111) AS result UNION ALL

SELECT 12 AS ID , Convert(VARCHAR(30), GETDATE(), 12 ) AS result UNION ALL

SELECT 112 AS ID , Convert(VARCHAR(30), GETDATE(), 112) AS result UNION ALL

SELECT 13 AS ID , Convert(VARCHAR(30), GETDATE(), 13 ) AS result UNION ALL

SELECT 113 AS ID , Convert(VARCHAR(30), GETDATE(), 113) AS result UNION ALL

SELECT 14 AS ID , Convert(VARCHAR(30), GETDATE(), 14 ) AS result UNION ALL

SELECT 114 AS ID , Convert(VARCHAR(30), GETDATE(), 114) AS result UNION ALL

SELECT 20 AS ID , Convert(VARCHAR(30), GETDATE(), 20 ) AS result UNION ALL

SELECT 120 AS ID , Convert(VARCHAR(30), GETDATE(), 120) AS result UNION ALL

SELECT 21 AS ID , Convert(VARCHAR(30), GETDATE(), 21 ) AS result UNION ALL

SELECT 121 AS ID , Convert(VARCHAR(30), GETDATE(), 121) AS result

以上就是关于如何把access中的yyyymmdd字符串，转化为yyyy/mm/dd 用sql实现。谢谢了全部的内容，包括:如何把access中的yyyymmdd字符串，转化为yyyy/mm/dd 用sql实现。谢谢了、SQL 怎么把DD-MM-YYYY格式的转换成YYYY/MM/DD、SQLserver怎样将日期类型转换为yyyyMMddHH24miss等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！