Load date into SQL Server - datetime datatype)

[mickboland]

I get the following error trying to load a date value into a datetime field in SQL Server (using SQL Server odbc driver).

I have tried the folowing:
1. left target as date and used iconv to convert the date
2. made target a string and used iconv to convert the date
3. Used oconv to format the data in the default string format for dates

Any one solved this problem? Seems fairly simple - just need to lod data into a date field in sql server?!?!!?


SuncorpPersonal..xfm.lkDataBaseIn1: DSD.BCIPut call to SQLExecute failed.
SQL statement:INSERT INTO "Bank_Transactions"("Bank", "Transaction_Date", "Description", "Amount", "Balance") VALUES (?,?,?,?,?)
SQLSTATE=23000, DBMS.CODE=273
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Bank = "ANZ"
Transaction_Date = 13776
Description = "EFTPOS WDL PHARMACY SAMFORD QL"
Amount = -127.2
Balance = 64.29

[Bala R]

Try Oconv and have the target field as varchar or timestamp. SQL Server stores date as timestamps. You get this error either because you selected Date as the column type in your stage or the date is in DS internal format.

[mdan]

Use Oconv(Iconv()). Check documentation for formatting. Always works.

[mickboland]

I use oconv(iconv(lkTransactions.TransactionDate,"D/DMY"),"D2") but still get the error. I have tried lots of different date formats.

Do i need to do something on the SQL server side?

[chulett]

From what I recall, to load into a SQL Server "datetime" you need to define your data type in the job as a Timestamp and then get your date in:

Code: Select all

YYYY-MM-DD HH24:MI:SS.TTT

format, where .TTT is milliseconds which must be included. Is that something you tried?

[ray.wurlod]

Code: Select all

Oconv(InLink.Transaction_Date, "D-YMD[4,2,2]") : " 00:00:00.000"

[Bala R]

SQLSTATE=23000, DBMS.CODE=273
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot insert a non-null value into a timestamp column. Use INSERT with a column list or with a default of NULL for the timestamp column.
SQLSTATE=37000, DBMS.CODE=8180
[DataStage][SQL Client][ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Bank = "ANZ"
Transaction_Date = 13776
Description = "EFTPOS WDL PHARMACY SAMFORD QL"
Amount = -127.2
Balance = 64.29

SQLSTATE=23000 is a integrity constraint violation. Check to see if the Transaction_Date field is a special case timestamp field that is autogenerated in DB. You can identify it by loading the metadata - It will show sqltype as Binary.

[Luk]

I have also problems with loading dates into Microsoft SQL server.

I changed date column from datetime to varchar - but only in DataStage metadata, in target I didn't change anything (colums are datetime, or timestamp type). Of course you must first prepare a proper format of date using Oconv(),Iconv(). and in my case everything works fine.

Also I have problem with double quotation ("...") using ODBC stage. I must use single quotation('...') or use Dynamic RDBMS stage.

hope it helps

regards

[bmadhav]

There is a SHORT DATETIME field u select from the datatypes selection. i have used this to load dates into SQL server tables. it works! :D

Bindu

[Bala R]

SQL server is more lenient when you want to insert a date than other DBs(Oracle, DB2). If you pass the data in as a varchar in the format that matches the date format of the field, SQL server would do the cast for you internally. No need to append the time part. Inserting a date shouldn't be such a hassle. Please check with your DBA to know whats going on with the Transaction_Date field. Let us know what information you got.

[javier perez]

I believe the following will work if you don't need a full timestamp (based on using an ODBC stage).
In the transfom stage that loads the database use the following expression ... Oconv(DateGenericToInternal(InputLink.DateField), "D-YMD[4,2,2]") : " 00:00" ...
and change the Data Element metadata tag to SQL.SMALLDATETIME,
and change the SQL Type metadata tag to Varchar(23)

With this defeinition, I let DataStage create the table and the DDL generated for the table looks something like ...
CREATE TABLE TEMP_LOADDATETEST("RowID" varchar(10) NOT NULL, "DateFormatA" smalldatetime NOT NULL, "DateFormatB" varchar(10) NOT NULL, "DateFormatC" varchar(10) NOT NULL, "DateFormatD" smalldatetime NOT NULL);

and the SQL to load the data looks something like ...
INSERT INTO TEMP_LOADDATETEST("RowID", "DateFormatA", "DateFormatB", "DateFormatC", "DateFormatD") VALUES (?,convert(smalldatetime, ?),?,?,convert(smalldatetime, ?));

Note that SQL Server is using the Convert function with the smalldatetime definition.