Sql-server – ORA 01861 error

linked-serveroraclesql server 2014

I am reading a date from a linked serve and it returns the field in the datetime2 format. Here is my simplified query:

select * from openquery(LinkedServer,
    'select QCTL.INVC_HEADER.POST_DATE
    FROM  QCTL.INVC_HEADER ')

When I copy one of the records returned by the query to use it as input parameter, I get an ORA 10861 error:

ORA-01861: literal does not match format string

    select * from openquery(LinkedServer,
    'select QCTL.INVC_HEADER.POST_DATE
    FROM  QCTL.INVC_HEADER 
    WHERE
    QCTL.INVC_HEADER.POST_DATE < ''2004-09-03 00:00:00.0000000''')

Please advise how to get around this

Best Answer

There's a problem with the hard-coded date. You can't simply put it as literal on Oracle (unlike SQL Server).

  • You can try using a TO_DATE() conversion function:

    select * from openquery(LinkedServer,
    'select QCTL.INVC_HEADER.POST_DATE
    FROM  QCTL.INVC_HEADER 
    WHERE
    QCTL.INVC_HEADER.POST_DATE < TO_DATE(''2004-09-03'', ''YYYY-MM-DD'')')

  • Placing the timestamp keyword just behind the literal will make it work also.

    select * from openquery(LinkedServer,
    'select QCTL.INVC_HEADER.POST_DATE
    FROM  QCTL.INVC_HEADER 
    WHERE
    QCTL.INVC_HEADER.POST_DATE < timestamp ''2004-09-03 00:00:00.0000000''')
Related Question