Converting Oracle TIMESTAMP(6) TO SQL SERVER 2008 DATETIME2(6)
I am bulk importing a csv file to SQL server 2008, the csv file has been generated from exporting the table data from Oracle SQL developer. The data for one column in that csv file is in TIMESTAMP(6) for which I am having the DATETIME2(6) datatype for the required column in the SQL server 2008. I am importing the CSV file using the below statement
USE H_CLAIMS GO BULK INSERT H_CLAIMS.dbo.APPLICATION_QUEUES FROM 'D:\MyWork\HC DB Work\HCAIDDB_CSV_EXPORTS\APPLICATION_QUEUES_export.CSV'
WITH (FIELDTERMINATOR = ',', ROWTERMINATOR = '\n') GO
while doing above I am getting the below error Msg 4864, Level 16, State 1, Line 1 Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 5 (CREATED_DATE). Msg 4864, Level 16, State 1, Line 1
The sample data in the column mentioned in the error is like 21-NOV-14 08.57.51.565214000 AM
So I am looking for the answer, which can overcome this issue with any other attributes during the bulk insert statement or any convert function which can properly convert the datetime in the sample data to SQL SERVER 2008 datetime2 format.
SQL Server doesn't know how to convert the text value " 21-NOV-14 08.57.51.565214000 AM" to a DATETIME2 column. Try it in a query analyser window :
SELECT CAST('21-NOV-14 08.57.51.565214000 AM' AS DATETIME2(6))
Note that if you're using DATETIME2(6) it'll be loosing precision compared to what you're trying to import. Have a look at http://msdn.microsoft.com/en-GB/library/bb677335.aspx.
When I've had to do this coming from DB2 text files, I've done it two different ways.
- Import the datetime field into a varchar then written a bit of SQL to manipulate the string into a format SQL Server can recognise, so something like. Bit slow and clunky, especially if you have a lot of data.
- Use SSIS and create a transformation to do the string manipulation there. This has the advantage of still being able to bulk insert into the destination table, but does mean you need to be able to have access to integration services.
Converting Oracle TIMESTAMP(6) TO SQL SERVER 2008 , SQL Server doesn't know how to convert the text value " 21-NOV-14 08.57. 51.565214000 AM" to a DATETIME2 column. Try it in a query� QD_RDB_ENTRY_DATE" TIMESTAMP (6) DEFAULT SYSTIMESTAMP. The value of this column in the first record is: 09-JUL-12 01.03.53.009438000 AM. I have the column in a SQL Server Table defined as
As I couldn't find any bulk Insert which will do the work for me, I have gone with a different approach. After many trails with cast and convert, I followed the below approach which is working as expected I have created a function which can convert the oracle timestamp(6) to nvarchar of sql which can be directly inserted as datetime2(6) datatype in sql server 2008. Below is the function Then I have used a stored procedure which can accept the file path as input parameter and a temp table to hold the nvarchar based datetime2 value. In the stored procedure I have used the dynamic bulk insert statement to insert into the required table. The procedure is after the function
CREATE FUNCTION DATETIMECONVERTER ( @ORACLETIMESTAMP NVARCHAR(100) )RETURNS nvarchar(100) AS BEGIN DECLARE @convertedString nvarchar(100); select @convertedString= replace(@ORACLETIMESTAMP,'.',':'); RETURN STUFF(@convertedString, CHARINDEX(':', @convertedString,18), 1, '.') END GO CREATE PROCEDURE IMPORT_APPLICATION_ROLES @PATH varchar(1000) AS IF OBJECT_ID('H_CLAIMS.DBO.TEMP_APPLICATION_QUEUES', 'U') IS NOT NULL DROP TABLE H_CLAIMS.DBO.TEMP_APPLICATION_ROLES CREATE TABLE H_CLAIMS.DBO.TEMP_APPLICATION_ROLES ( ROLE_ID INT NOT NULL, ROLE_NAME NVARCHAR(255), ROLE_DESC NVARCHAR(255), CREATED_BY NVARCHAR(100), CREATED_DATE NVARCHAR(100), UPDATED_BY NVARCHAR(100), UPDATED_DATE NVARCHAR(100) ) DECLARE @bulkInsert NVARCHAR(4000) = 'BULK INSERT TEMP_APPLICATION_ROLES FROM ''' + @PATH + ''' WITH ( FIELDTERMINATOR ='','', ROWTERMINATOR =''\n'' )'; EXEC(@bulkInsert) INSERT INTO APPLICATION_ROLES (ROLE_ID,ROLE_NAME,ROLE_DESC,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE) SELECT ROLE_ID,ROLE_NAME,ROLE_DESC,CREATED_BY,dbo.DATETIMECONVERTER(CREATED_DATE)AS CREATED_DATE, UPDATED_BY,dbo.DATETIMECONVERTER(UPDATED_DATE) AS UPDATED_DATE FROM H_CLAIMS.dbo.TEMP_APPLICATION_ROLES DROP TABLE H_CLAIMS.DBO.TEMP_APPLICATION_QUEUES GO
to execute the statment I have used the below statement
EXEC H_CLAIMS.DBO.IMPORT_APPLICATION_QUEUES @PATH='D:\my_export.CSV';
TO_CHAR - Convert Datetime to String, In SQL Server, you can use CONVERT or CAST functions to convert In Oracle, TO_CHAR function converts a datetime value (DATE, TIMESTAMP data types i.e. ) to a string using the specified format. DATETIME2 data types i.e.) to a string. Oracle: -- Convert the current 4, YYYYMM, VARCHAR(6), 112. I am bulk importing a csv file to SQL server 2008, the csv file has been generated from exporting the table data from Oracle SQL developer. The data for one column in that csv file is in TIMESTAMP(6) for which I am having the DATETIME2(6) datatype for the required column in the SQL server 2008. I am importing the CSV file using the below statement
Make sure to place the .csv files in the server machines drive while executing the stored procedure
DATETIME or DATETIME2(3)?, Starting from SQL Server 2008, you can use DATETIME2(p) data type with fractional seconds precision up to 7 digits (0.1 microseconds) in Microsoft SQL Server. Although it COBOL to Oracle PL/SQL Note .015 to .017 conversion SELECT CAST('2017-12-29 11:22:38.015' AS DATETIME) # 2017-12-29� DATETIME2 DATETIME; DATETIME2 has a date range of "0001 / 01 / 01" through "9999 / 12 / 31". January 1,1 CE through December 31, 9999 CE. DATETIME type only supports year 1753 to 9999.
I may be late to answer this but allow me to give you my workaround (if the precision doesn't really matter) I import the timestamp from oracle table into SQL 2008 varchar then I update the varchar into a format that will fit for datetime2 then I alter the SQL table column to data type datetime2. EG: in case you have time stamp like '01-JAN-15 12.00.00.000000000 AM +05:30'
update My_Table set MyTimeStamp = substring(MyTimeStamp, 1,10)+ REPLACE(substring(MyTimeStamp, 11, 8),'.',':')+ substring(MyTimeStamp, 19, 13) where MyTimeStamp like '%.%.%.%'; alter table [My_Table] alter column MyTimeStamp DATETIME2; Go;
datetime2 (Transact-SQL), Storage size, 6 bytes for precision less than 3. 7 bytes When you convert to date and time data types, SQL Server rejects all values it cannot� The destination oracle_table has a TIMESTAMP(6) column which stores date to more granular level (for ex: 01-JAN-0001 02.30.32.001001 PM). when I use the oracle_table as a destination, I get an
Oracle timestamp datatype data migration to MS SQL Server, Question by taj � Mar 11, 2015 at 07:14 AM � sql-server-2008oracle **i have oracle data with datatype timestamp, that i have to migrate/dump to ms sql having column datetime2 using linked server but getting data conversion error. is there� In this case, SQL Server performs an implicit conversion behind the scenes when we try to assign the datetime2 value to a datetime variable. We can see that the datetime variable has less fractional seconds precision, and we end up with a fractional part of 123 even though the original fractional part was 1234567 .
Converting Oracle Timestamp with 9 fractions of second , I have the column in a SQL Server Table defined as Datetime2(6). The precision is 6 which is the maximum. When I load the table the SSIS� If the server type is datetime or smalldatetime, the value corresponds to the TDS wire format and will be a 4-byte value for smalldatetime and an 8-byte value for datetime. If the server type is datetime2, the value is returned as a SQL_TIMESTAMP_STRUCT.
Converting Oracle timestamp(6) to datetime - sql-server-2008, I think you are probably looking for datetime2(6) in SQL Server 2008. Defines a date that is combined with a time of day that is based on 24-hour clock. The format data types that are supported are numeric (FLOAT, INT), character, date, time, and timestamp (DATETIME). La syntaxe de la fonction CAST est la suivante : SELECT 6 Dec 2016 The Oracle CAST function allows you to convert one data type to another. 687). Dec 11, 2017 · In SQL Server 2008, Microsoft introduced a new data-type “date”.