Question

将SQL查询出来的VARCHAR格式的8位数转换为yyyy-mm-dd格式 是db2数据库 用DbVisualizer8.0.8客户端

SELECT
PROD_FOUND_DATE AS "beginDate",
SCHEDULE_DUEDATE_SECOND AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1

Answer 1

SELECT
to_char(PROD_FOUND_DATE,'yyyy-mm-dd') AS "beginDate",
SCHEDULE_DUEDATE_SECOND AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1

追问

报这个错
DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=Invalid date/time value;SYSIBM.VARCHAR_FORMAT, DRIVER=3.57.82

追答

PROD_FOUND_DATE先把这个转换为date类型

追问

不会 麻烦把SQL给我写一下 O(∩_∩)O谢谢

追答

SELECT
to_char(DATE(PROD_FOUND_DATE),'yyyy-mm-dd') AS "beginDate",
SCHEDULE_DUEDATE_SECOND AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1
看看是不是 我没用过db2

SELECT
to_char(to_DATE(PROD_FOUND_DATE,'当前的格式'),'yyyy-mm-dd') AS "beginDate",
SCHEDULE_DUEDATE_SECOND AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1

追问

还是报 DB2 SQL Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=3.57.82的错

追答

SELECT
to_char(to_date(PROD_FOUND_DATE,'当前的格式'),'yyyy-mm-dd') AS "beginDate",
SCHEDULE_DUEDATE_SECOND AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1

这个呢

Answer 2

SELECT
to_date(PROD_FOUND_DATE,'yyyy-mm-dd') AS "beginDate",
to_date(SCHEDULE_DUEDATE_SECOND,'yyyy-mm-dd') AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1

追问

报错SQLCODE=-20448, SQLSTATE=22007, SQLERRMC= ;yyyy-mm-dd, DRIVER=3.57.82

追答

SELECT
to_date(PROD_FOUND_DATE,'yyyymmdd') AS "beginDate",
to_date(SCHEDULE_DUEDATE_SECOND,'yyyymmdd') AS "endDate"
FROM
HBTS.TTMXX20
WHERE
1=1

追问

我说大神有用吗 还是一样的错。。。。。。

追答

SELECT SUBSTR(PROD_FOUND_DATE, 1, 4) || '-' ||
SUBSTR(PROD_FOUND_DATE, 5, 2) || '-' ||
SUBSTR(PROD_FOUND_DATE, 7, 2) AS "beginDate",
SUBSTR(SCHEDULE_DUEDATE_SECOND, 1, 4) || '-' ||
SUBSTR(SCHEDULE_DUEDATE_SECOND, 5, 2) || '-' ||
SUBSTR(SCHEDULE_DUEDATE_SECOND, 7, 2) AS "endDate"
FROM HBTS.TTMXX20WHERE 1 = 1;
哎，想多了