CODEWITHSUNDEEP
sqloracle-databasedatedatetimeoracle-sqldeveloper
Kalpesh Koli
Kalpesh Koli

Reputation: 87

Convert oracle timestamp to DD-MM-YYYY

Need help.

I am trying to pull data from toad where the one column (EFFECTIVE_FROM) contains the date in timestamp dd/mm/yy hh:mi:ss:mmmAM e.g. (5/8/2015 2:14:19 PM) but i want that date in DD-MM-YYYY i.e. 08-May-2015

I am using the below query

Please Help

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'

Upvotes: 0

Views: 13161

Answers (3)

Mark Leiber
Mark Leiber

Reputation: 3138

You don't want 'MM' if you want the month written out like your example (08-May-2015). MM will give you the month number, so you'll wind up with '08-05-2015'.

Use:

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

See the format models here: http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements004.htm

Upvotes: 0

TobyLL
TobyLL

Reputation: 2296

Assuming that EFFECTIVE_FROM is a date or timestamp field, you can use the TO_CHAR function:

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'

Upvotes: 1

majk
majk

Reputation: 183

You can use TO_DATE() as in:

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'

Upvotes: 0

Related Questions