Oracle中转换时间戳格式以适配SQL Server datetime字段

Hey Neha, I’ve got you covered! Converting that Oracle timestamp string to the exact format SQL Server’s datetime field accepts is straightforward with Oracle’s built-in date functions. Let’s break it down:

Step-by-Step Solution #

First, we need to parse your original timestamp string into an Oracle timestamp type, then format it to match SQL Server’s required YYYY-MM-DD HH:MI:SS.FFF pattern (with only 3 milliseconds digits, since SQL Server’s datetime doesn’t support more precision).

Here’s the code for a static example:

SELECT TO_CHAR(
         TO_TIMESTAMP('01-APR-21 12.02.00.496677000 AM', 'DD-MON-RR HH.MI.SS.FF9 AM'),
         'YYYY-MM-DD HH24:MI:SS.FF3'
       ) AS sql_server_compatible_datetime
FROM DUAL;

If you’re pulling data from a table, replace the static string with your column name:

SELECT TO_CHAR(
         TO_TIMESTAMP(your_timestamp_column, 'DD-MON-RR HH.MI.SS.FF9 AM'),
         'YYYY-MM-DD HH24:MI:SS.FF3'
       ) AS formatted_datetime
FROM your_target_table;

A Quick Note on Language Settings #

If your Oracle instance uses a non-English NLS_DATE_LANGUAGE setting, you’ll want to explicitly specify English for parsing the month abbreviation (like 'APR') to avoid errors:

SELECT TO_CHAR(
         TO_TIMESTAMP(
           '01-APR-21 12.02.00.496677000 AM', 
           'DD-MON-RR HH.MI.SS.FF9 AM', 
           'NLS_DATE_LANGUAGE=ENGLISH'
         ),
         'YYYY-MM-DD HH24:MI:SS.FF3'
       ) AS sql_server_compatible_datetime
FROM DUAL;

This will output exactly 2021-04-01 00:02:00.496 (note that HH24 converts the AM/PM time to 24-hour format, which works perfectly for SQL Server—both 12-hour and 24-hour formats are accepted as long as the structure matches).

内容的提问来源于stack exchange，提问作者Neha Singh