vmandem
asked on
How to format the oracle date to get the YYMMDD from SQLSERVER
I have the below query and I'm trying to get the results from Oracle database using
linked server in Sqlserver. I'm trying to use the to_date function to get the YYMMDD
format but I'm getting the below error. Please let me know how to get full date.
ERROR:
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: "MMYYDD": invalid identifier
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
MY QUERY:
SELECT *
FROM OPENQUERY (GL_PROD_WRITE, 'SELECT Invoice_Number, Billed_Road, Invoice_Date, Rptg_Mk, Car_Num, Ref_Document, Repairing_Party, Currency, Detail_Source,
Car_Type, Load_Empty_Ind, to_date(Ship_Date,"MMYYDD"
Applied_Job_Cd, Applied_Qualifier, Why_Made, Removed_Job_Code, Removed_Qualifier, Resp, Defect_Card_Party, Defect_Card_Date,
Material_Sign, Machine_Priceable, Wrong_Repair, Narrative, Car_Class, FM_Cd, Mobile_Repair, ARI_Cust_Number, Currency_Multiplier,
Labor_AMT_US_Dols, Material_Amt_US_Dols, C_BRCEXT_SHP_CD, Condition FROM GL_PROD.C_BRCEX
linked server in Sqlserver. I'm trying to use the to_date function to get the YYMMDD
format but I'm getting the below error. Please let me know how to get full date.
ERROR:
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: "MMYYDD": invalid identifier
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
MY QUERY:
SELECT *
FROM OPENQUERY (GL_PROD_WRITE, 'SELECT Invoice_Number, Billed_Road, Invoice_Date, Rptg_Mk, Car_Num, Ref_Document, Repairing_Party, Currency, Detail_Source,
Car_Type, Load_Empty_Ind, to_date(Ship_Date,"MMYYDD"
Applied_Job_Cd, Applied_Qualifier, Why_Made, Removed_Job_Code, Removed_Qualifier, Resp, Defect_Card_Party, Defect_Card_Date,
Material_Sign, Machine_Priceable, Wrong_Repair, Narrative, Car_Class, FM_Cd, Mobile_Repair, ARI_Cust_Number, Currency_Multiplier,
Labor_AMT_US_Dols, Material_Amt_US_Dols, C_BRCEXT_SHP_CD, Condition FROM GL_PROD.C_BRCEX
ASKER
Hi, whether you noticed or not that my Whole query is already in single quotes and If I give single quotes again around the format('MMDDYYYY') it is giving me Incorrect Syntax near 'MMDDYYYY'. If I remove the quotes like MMDDYYYY then I get the error
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: "MMDDYYYY": invalid identifier
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
Please let me know what to do in this scenario.
An error occurred while preparing a query for execution against OLE DB provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00904: "MMDDYYYY": invalid identifier
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare returned 0x80040e14].
Please let me know what to do in this scenario.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Good
See: http://www.dba-oracle.com/f_to_date.htm