title: How can I get a date from SQL Server into Oracle 12c? Dates are being inverted (backwards)
body: <p>I'm having a real problem getting a date from <code>SQL Server</code> into <code>Oracle</code> while maintaining the correct value.</p>
  
  <p>The value in SQL Server looks like: <code>"Apr 28 1969 12:00AM"</code>
  When I pull this value into a <code>.NET</code> <em>DateTime</em> it looks like: <code>"04/28/1969 12:00AM"</code>
  When it gets inserted into Oracle it looks like: "28-APR-19"</p>
  
  <p>The Oracle date looks correct at quick glance, but if I do a <code>TO_CHAR(DATE, 'MM/DD/YYYY')</code> I get "<code>04/28/6919</code>" &lt;---- The year is backwards!!</p>
  
  <p>Here's a set of dates from Oracle:</p>
  
  <pre><code>01/18/5919
  09/19/8819
  02/13/5619
  08/30/5819
  04/28/6519
  08/22/6919
  10/24/6119
  02/27/6919
  02/28/6019
  12/20/6219
  09/28/3619
  10/02/6219
  </code></pre>
  
  <p>All years end in '19' because they are all backwards!</p>
  
  <p>Because Oracle thinks every one of my years ends with "19" it thinks all leap years are invalid and I have a large set of data I can't even get inserted (not to mention the bad dates)</p>
  
  <p>I'm using a simple stored proc to get the data out of SQL Server, the data is then stored in a simple POCO. I'm using <code>Oracle.DataAccess.OracleBulkCopy</code> to do my actual insert using the DataTable. I have no control over how I retrieve the data or how I save it... but I can manipulate it in between.</p>
  
  <p>So far I've tried returning the date as a string and doing formatting on it (<code>dd-MMM-yyyy</code>) and (<code>yyyymmdd</code>) - neither worked. I've also tried setting the date to null if it was a leap year and trying to set it directly afterwards... it's a hack, but that didn't do any good either.</p>
  
  <p>Any help is appreciated.</p>
  
  <p>My stored proc:</p>
  
  <blockquote>
    <p>USE [InsuranceFileProcessing]
    GO
    SET ANSI_NULLS OFF
    GO
    SET QUOTED_IDENTIFIER OFF
    GO
    ALTER PROCEDURE [dbo].[P_GET_INSURANCE_POLICY_HOLDERS_FOR_DATA_UPLOAD]
           @INSURANCE_COMPANY_CODE VARCHAR(5) 
         , @INSURANCE_FILE_UPLOAD_LOG_ID  INT
         , @START_ROW_NUM INT
         , @END_ROW_NUM INT
         , @GET_LEAP_YEARS BIT
    AS 
    BEGIN 
        DECLARE @ERROR_NUMBER INT 
        DECLARE @ERROR_SEVERITY INT 
        DECLARE @ERROR_STATE INT 
        DECLARE @ERROR_PROCEDURE NVARCHAR(100) 
        DECLARE @ERROR_LINE INT 
        DECLARE @ERROR_MESSAGE NVARCHAR(4000) </p>
  </blockquote>
  
  <pre><code>SET NOCOUNT ON 
  
  BEGIN 
      BEGIN TRY 
            SELECT   DISTINCT                  
                IT.INSURANCE_COMPANY_CODE             INS_COMPANY_NUM
              , IP.POLICY_NUMBER                          INS_POLICY_NUM              
              , PH.FIRST_NAME                             PH_FIRST_NAME
              , PH.MIDDLE_NAME                            PH_MIDDLE_NAME
              , PH.LAST_NAME                              PH_LAST_NAME
              , LEFT(PH.NAME_SUFFIX,1)                    PH_NAME_SUFFIX
              , PH.ADDRESS                                PH_ADDRESS
              , PH.CITY                                   PH_CITY  
              , PH.STATE                                  PH_STATE  
              , PH.ZIPCODE                                PH_ZIP_CODE
              , CONVERT(VARCHAR, PH.DOB, 100)             PH_DATE_OF_BIRTH
              , PH.GENDER                                 PH_GENDER
              , PH.FL_DLN                                 INS_DL_NUMBER               
              , PH.FED_TIN                                INS_FEID
              , PH.FL_DLN_CROSS_REF                       FL_DLN_CROSS_REF
              , PH.FL_DLN_GENERATED                       FL_DLN_GENERATED
              , PH.NON_STRUCTURED_NAME                    PH_NON_STRUCT_NAME  
              , PH.EFFECTIVE_DATE                         EFFECTIVE_DATE      
              , ( CASE PH.COMPANY_INDICATOR 
                  WHEN 'Y' THEN 'F'                                   
                  WHEN 'N' THEN 'T'
                  ELSE 'T'
                  END )                                   PERSONAL_FLAG           
              --, CONVERT(VARCHAR(12),IP.UPDATE_TS,110)       INSERT_TIMESTAMP    
              , IP.CREATED_TS                             INSERT_TIMESTAMP    
              , IDENTITY(INT,1,1) AS                      ROWNUM
              , ph.CUSTOMER_NUMBER                        CUSTOMER_NUMBER
  
  
               INTO
                            #UPLOAD_POLICY_HOLDER
  
              FROM INSURANCE_TRANSACTION IT
                  INNER JOIN INSURANCE_COMPANIES IC ON IC.INSURANCE_COMPANY_ID = IT.INSURANCE_COMPANY_ID
                  INNER JOIN INSURANCE_POLICY IP ON IT.INSURANCE_POLICY_ID = IP.INSURANCE_POLICY_ID
                  INNER JOIN POLICY_HOLDER PH ON IP.INSURANCE_POLICY_ID = PH.INSURANCE_POLICY_ID              
              WHERE IT.INSURANCE_COMPANY_CODE     = @INSURANCE_COMPANY_CODE
                  AND IT.INSURANCE_FILE_UPLOAD_LOG_ID  =  @INSURANCE_FILE_UPLOAD_LOG_ID
                  AND IT.HAS_ERROR = 0 
                  AND PH.HAS_ERROR = 0
                  AND ((LEFT(REPLACE(CONVERT(VARCHAR(10), DOB, 101), '/', ''), 4) = '0229' AND @GET_LEAP_YEARS = 1)
                      OR (LEFT(REPLACE(CONVERT(VARCHAR(10), DOB, 101), '/', ''), 4) &lt;&gt; '0229' AND @GET_LEAP_YEARS = 0))
              ORDER BY  IT.INSURANCE_COMPANY_CODE , IP.POLICY_NUMBER  ;
  
               SELECT * FROM #UPLOAD_POLICY_HOLDER 
                      WHERE ROWNUM &gt; @START_ROW_NUM AND ROWNUM &lt;= @END_ROW_NUM 
                      ORDER BY ROWNUM;
  
                      IF EXISTS
                      (
                      SELECT *
                      FROM tempdb.dbo.sysobjects
                      WHERE ID = OBJECT_ID(N'tempdb..#UPLOAD_POLICY_HOLDER')
                      )
                      BEGIN
                            DROP TABLE #UPLOAD_POLICY_HOLDER
                      END
  
  
      END TRY 
  
      BEGIN CATCH 
          IF @@TRANCOUNT &gt; 0 
              ROLLBACK TRANSACTION 
  
              SET @ERROR_NUMBER = ERROR_NUMBER() 
              SET @ERROR_SEVERITY = ERROR_SEVERITY() 
              SET @ERROR_STATE = ERROR_STATE() 
              SET @ERROR_PROCEDURE = ERROR_PROCEDURE() 
              SET @ERROR_LINE = ERROR_LINE() 
              SET @ERROR_MESSAGE = ERROR_MESSAGE() 
  
              EXEC P_INSERT_SQL_ERROR @ERROR_NUMBER, @ERROR_SEVERITY, @ERROR_STATE, @ERROR_PROCEDURE, @ERROR_LINE, @ERROR_MESSAGE 
      END CATCH 
  
  END 
  </code></pre>
  
  <p>END</p>
  
  <p>You can ignore the Leap_Year stuff - that was a separate attempt I'd made. The PH.DOB field is the one I'm having issues with. Previously I was just returning the DOB field, the CONVERT() was my attempt to get the value as a string so I could have more control.</p>
  
  <p>My insert into Oracle code:</p>
  
  <blockquote>
    <p>private void LoadDataIntoHSMVDBBulk(DataTable dt, string DestinationTableName, List ColumnMappings)
            {
                using (var bc = new OracleBulkCopy(GetConnectionString()))
                {
                    bc.DestinationTableName = DestinationTableName;
                    bc.BulkCopyOptions = OracleBulkCopyOptions.UseInternalTransaction;</p>
  </blockquote>
  
  <pre><code>            foreach (var colmapping in ColumnMappings)
              {
                  var split = colmapping.Split(new[] { ',' });
                  bc.ColumnMappings.Add(split.First(), split.Last());
              }
  
              bc.BulkCopyTimeout = 20000;
              bc.BatchSize = GetOracleBulkCountFromConfig();
              bc.NotifyAfter = GetOracleBulkCountFromConfig();
              bc.OracleRowsCopied += new OracleRowsCopiedEventHandler(bulkCopy_OracleRowsCopied);
              bc.WriteToServer(dt);
              bc.Close();
              bc.Dispose();
              dt.Clear();
          }
      }
  </code></pre>
  
  <p>I took an old copy of our project from several months ago and the dates are correct!! It seems the culprit is our version of the Oracle.DataAccess. When I point to Oracle 11g the dates are good, when I point to the newer Oracle 12c the dates are inverted. Any help is appreciated, but for now we're rolling the servers back to 11g.</p>