0

I am trying to use convert function to convert date field to varchar but it keep giving missing expression error.

My Query: 

select INVENTORY_ITEM_ID, 
       convert(varchar(20), IMPLEMENTATION_DATE,110) 
from apps.mtl_item_revisions  
where INVENTORY_ITEM_ID=21928;

gives error:

ORA-00936: missing expression
00936. 00000 - "missing expression"
*Cause:
*Action: Error at Line: 97 Column: 35

Ankit Bajpai
  • 9,548
  • 4
  • 18
  • 38
Princei
  • 1
  • 1
  • 1
  • Use [TO_CHAR](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions180.htm). `CONVERT` is used to change the character set of a string. – Wernfried Domscheit Aug 01 '17 at 07:34
  • In all cases - whenever you use this date (IMPLEMENTATION_DATE) in an insert for example, into a VARCHAR2 column, Oracle would implicitly convert it to_char. – g00dy Aug 01 '17 at 08:17
  • TO_CHAR is not supported in Escape sequence. I want to use this(CONVERT) in Notes SQL which is an ODBC driver. – Princei Aug 01 '17 at 08:20
  • "TO_CHAR is not supported in Escape sequence". don't understand what you mean by that. The way you want to use that convert works in Sql Server, not in Oracle. If you want to change date to char, you use the to_char function. – Renato Afonso Aug 01 '17 at 08:32
  • Is it possible that there is a setting in the NotesSQL driver that makes it believe it is pointing to a SQLServer DB rather than an Oracle DB ? Fundamentally you cannot use the CONVERT function as shown to store a date as a character datatype in Oracle. Use the TO_CHAR function instead. – BriteSponge Aug 01 '17 at 10:40
  • "TO_CHAR is not supported in Escape sequence". I meant Transact-SQL for ODBC – Princei Aug 03 '17 at 11:52

1 Answers1

-1

The following line is Microsoft SQL Server syntax, not Oracle syntax:

convert(varchar(20), IMPLEMENTATION_DATE,110)

It doesn't matter what kind of database driver you use, or who wrote it. The query is being sent to Oracle with SQLServer syntax and Oracle is giving the error. (Also, while it might be required in a query tool, be wary of terminating oracle SQL statements with semicolon; in some languages (e.g. C#, or dynamic SQL within PL/SQL) you may get an unexpected character error.)

Change your SQL to this:

select INVENTORY_ITEM_ID, 
   to_char(IMPLEMENTATION_DATE, 'mm-dd-yyyy') as IMPLEMENTATION_DATE
from apps.mtl_item_revisions  
where INVENTORY_ITEM_ID=21928

That's the ORacle equivalent of SQLServer 110 date format

Boneist
  • 21,504
  • 1
  • 23
  • 39
Caius Jard
  • 47,616
  • 4
  • 34
  • 62
  • 1
    `Do not terminate oracle SQLs with semicolon, in client apps; you may get an unexpected character error` -- depends on the client app, I guess. All of the GUIs I've ever used work fine (nay, even expect) a semi-colon to differentiate between different queries! – Boneist Aug 01 '17 at 15:31
  • Yes, but that's a function of the client app (golden, toad, etc) splitting and stripping. If it's put into code, much more likely the driver will pass it through as is, and oracle will balk at it.. I improved the clarity of my answer, thanks for the feedback! – Caius Jard Aug 02 '17 at 05:46
  • "If it's put into code"... er, PL/SQL expects a semi-colon so, y'know, #NotAllCode – Boneist Aug 02 '17 at 06:22
  • @Boneist Read this: https://stackoverflow.com/questions/12262145/ora-00911-invalid-character and then tell me how you'd like me to update my advice to fit with whatever it is you're skirting around but not saying directly. In fact; better still - just make an edit to my post rather than wasting everyone's time splitting hairs – Caius Jard Aug 02 '17 at 09:05
  • Wow, touchy much?! My last comment was merely a jokey reference to the fact that you appear to be mainly considering code that's outside the database, whereas if the OP is coding static queries in PL/SQL, your recommendation is actually inappropriate because it will cause compilation errors. You make a valid point for \*some\* means of passing sql statements across into the database, but not all, and we're trying to help the OP, not confuse them. – Boneist Aug 02 '17 at 09:37
  • A worthy edit; thanks. Apologies for misunderstanding the joke – Caius Jard Aug 02 '17 at 10:10