How to Specify Date Literal When Writing SQL Query from SQL Server That Is Linked to Oracle

Uniform SQL Date in Oracle SQL and SQL Server

Use this for something which works on both:

CAST('2015-09-14' as date)

The cast is executed only once so has no effect on performance.

Pass date column in open query view Oracle in SQL Server

I'd use the date literal syntax date '2020-06-29' so

select * 
  from openquery(Oracle,
                 'select "bs" from temp.views_employe where "Joining Date">= date ''2020-06-29'' ')

You could also use an explicit to_date

select * 
  from openquery(Oracle,
                 'select "bs" from temp.views_employe where "Joining Date">= to_date( ''2020-06-29'', ''YYYY-MM-DD'' ) ')

valid date format for sybase, oracle and sql server

Oracle will implicitly try to convert strings to dates using the TO_DATE( date_string, format_model, nls_params ) function. Without a format model, Oracle will use the default date format which is a session parameter (so is a per-user setting) stored in the NLS_SESSION_PARAMETERS table and you can find the default value using:

SELECT value
FROM   NLS_SESSION_PARAMETERS
WHERE  parameter = 'NLS_DATE_FORMAT'

This means the conversion will implicitly be:

TO_DATE(
  'Feb 13 2018 1:33AM',
  (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
)

and if the format model does not match your string then you will get an exception.

You can either set the output format to match Oracle's default or you could alter the default date format in the current Oracle session to match your current data using:

ALTER SESSION SET NLS_DATE_FORMAT = 'Mon DD YYYY HH12:MIAM';

You can also create a logon trigger to change the NLS settings when the user connects to the database and starts a session.

The other alternative is that instead of trying to use a string you use a timestamp literal, since you can specify a time component (which you can't with a date literal), and then let Oracle cast it back to a date:

TIMESTAMP '2018-02-13 01:33:00'

or you could explicitly call TO_DATE in your replication query for Oracle and specify the date format:

TO_DATE( 'Feb 13 2018 1:33AM', 'Mon DD YYYY HH12:MIAM' )

---

Related Topics