Search code examples
sql-serveroraclesybase

valid date format for sybase, oracle and sql server

I am working on a replication project where data is replicated on oracle as well as sql server from sybase database.

Basically one of the tables in sybase get populated with the data to be replicated

Sybase column contains data like -

{"a":"b","c":"d","created_date":"'Feb 13 2018 1:33AM'"}

So basically when we are forming query to replicate on sql server and oracle we are using date string 'Feb 13 2018 1:33AM' to convert into date for oracle and sql server.

This date string works fine with the sql server but failed with the error like -

ORA-01858 A NON NUMERIC CHARACTER WAS FOUND WHERE A NUMERIC WAS EXPECTED

So which date format should I use in Sybase so that it will work for both oracle and sql server to replicate.

Solution

  • 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' )