将oracle时间戳转换为DD-MM-YYYY

Question

需要帮助

我正试图从toad中提取数据，其中一列(effecve_from)包含时间戳dd/mm/yy hh:mi:ss:mmmAM中的日期，例如(5/8/2015 2:14:19 PM)，但我想要dd - mm - yyyy中的日期，即08-May-2015

我使用下面的查询

请帮

SELECT B.USER_NAME AS CREATED_BY, A.CREATION_DATE, C.USER_NAME, A.LAST_UPDATE_DATE, A.PFIZER_ITEMCODE, A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE,
A.ITEM_DESCRIPTION, A.BATCH_NUMBER, A.MFR_CODE, A.MFR_DESC AS MFR_DESCRIPTION, A.MFR_DATE, A.EXPIRY_DATE, A.EFFECTIVE_FROM  
FROM xxdhl_pf_batch_pricing A,
     fnd_user B,
     fnd_user c
where 1=1
AND A.CREATED_BY = B.USER_ID
AND A.LAST_UPDATED_BY = C.USER_ID
AND A.SYSTEM_ITEMCODE = 'xx' AND A.BATCH_NUMBER = 'yy'

Answer 1

假设EFFECTIVE_FROM是一个日期或时间戳字段，您可以使用TO_CHAR函数:

SELECT TO_CHAR(A.EFFECTIVE_FROM,'DD-MON-YYYY'), B.USER_NAME AS CREATED_BY, A.CREATION_DATE, C.USER_NAME, A.LAST_UPDATE_DATE, A.PFIZER_ITEMCODE, A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE,
A.ITEM_DESCRIPTION, A.BATCH_NUMBER, A.MFR_CODE, A.MFR_DESC AS MFR_DESCRIPTION, A.MFR_DATE, A.EXPIRY_DATE, A.EFFECTIVE_FROM
FROM xxdhl_pf_batch_pricing A,
     fnd_user B,
     fnd_user c
where 1=1
AND A.CREATED_BY = B.USER_ID
AND A.LAST_UPDATED_BY = C.USER_ID
AND A.SYSTEM_ITEMCODE = 'xx' AND A.BATCH_NUMBER = 'yy'

Answer 2

您可以使用TO_DATE()如下:

SELECT B.USER_NAME AS CREATED_BY,
   A.CREATION_DATE,
   C.USER_NAME,
   A.LAST_UPDATE_DATE,
   A.PFIZER_ITEMCODE,
   A.SYSTEM_ITEMCODE AS ORACLE_ITEM_CODE,
   A.ITEM_DESCRIPTION,
   A.BATCH_NUMBER,
   A.MFR_CODE,
   A.MFR_DESC AS MFR_DESCRIPTION,
   A.MFR_DATE,
   A.EXPIRY_DATE,
   to_date(A.EFFECTIVE_FROM, 'DD-MON-YYYY')
FROM xxdhl_pf_batch_pricing A, fnd_user B, fnd_user c
where 1 = 1
   AND A.CREATED_BY = B.USER_ID
   AND A.LAST_UPDATED_BY = C.USER_ID
   AND A.SYSTEM_ITEMCODE = 'xx'
   AND A.BATCH_NUMBER = 'yy'

Answer 3

如果您希望像您的示例(08-May-2015)那样写出月份，则不需要'MM'。MM会给你月份的数字，所以最后你会得到'08-05-2015'。

使用:

to_char(A.EFFECTIVE_FROM,'DD-MON-YYYY')

格式模型如下:http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm