Hi all,

I link my Oracle database from ODBS ms-query to Excel. I can see the date format in Ms-query was displayed as 2006-10-10 00:00:00, so when data prompt to excel the same format was displayed. In Excel, I had tried to changed the format to display as dd/mm/yyyy. However, when I re-query the data from parameter the format had been changed back to it what is original which is in year-mm-dd 00:00:00.

How am I suppose to fix the format in MS-query ? and How ?

Looking forward an early reply. Thanks.

Rgds
Lim

Re: Change Date format in MS-query

Lim,

A couple of possibilities I can think of :-

1. In MS Query press the SQL view. You should get a pop up box with the SQL statement. Find the part after the SELECT statement that refers to the date. It should look something like "YourTableName.Date". Carefully change this to "Format(YourTableName.Date,'mm/dd/yy')". You may then wish to add the word AS and a new name for the column (eg. Format(YourTableName.Date,'mm/dd/yy') AS TheNewHeading) .

2. Write a macro to change the format back after each load. (eg. Range("A2:A100").NumberFormat = "dd/mm/yy")

HTH
Carl

Re: Change Date format in MS-query

ORA-00904 means it does not recognise the date format you used it should be

dd/mm/yy or mm/dd/yy .... BUT NOT dd/mm/yyy

Make sure that "YourTableName" is replaced with the name of the table you are using and that "Date" is replaced by the field name that contains the date.
HTH
Carl

Re: Change Date format in MS-query

Hi Carl,

Sorry that's my typo error, here is my sql screen..

SELECT ACT_VW_PO_RECEV_INQ.ITEM_CODE, ACT_VW_PO_RECEV_INQ.ITEM_NAME, ACT_VW_PO_RECEV_INQ.SUPPLIER_CODE, ACT_VW_PO_RECEV_INQ.SUPP_NAME,
FORMAT(ACT_VW_PO_RECEV_INQ.GH_DT, 'DD/MM/YYYY'), ACT_VW_PO_RECEV_INQ.UNIT, ACT_VW_PO_RECEV_INQ.QTY, ACT_VW_PO_RECEV_INQ.GRN_NO, ACT_VW_PO_RECEV_INQ.REFERENCE, ACT_VW_PO_RECEV_INQ.PO_NO, ACT_VW_PO_RECEV_INQ.LOCATION, ACT_VW_PO_RECEV_INQ.GI_RATE, ACT_VW_PO_RECEV_INQ.CURRENCY, ACT_VW_PO_RECEV_INQ.EXCHANGE_RATE
FROM ORION.ACT_VW_PO_RECEV_INQ, ACT_VW_PO_RECEV_INQ

and I still getting the same error. Anything wrong here ?

Rgds
Lim

Re: Change Date format in MS-query

Looks Ok. I think this is something to do with Oracle which I do not know much about.

Try replacing the "FORMAT" with "to_date"

Carl

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login This site uses cookies. By continuing to browse this site, you are agreeing to our use of cookies. More Details Close