Load date into SQL Server - datetime datatype)
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 27
- Joined: Sun Mar 20, 2005 4:23 am
- Location: Brisbane, Australia
Load date into SQL Server - datetime datatype)
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
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
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.
Last edited by Bala R on Sun Sep 18, 2005 12:31 pm, edited 1 time in total.
Re: Load date into SQL Server - datetime datatype)
Use Oconv(Iconv()). Check documentation for formatting. Always works.
-
- Participant
- Posts: 27
- Joined: Sun Mar 20, 2005 4:23 am
- Location: Brisbane, Australia
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:
format, where .TTT is milliseconds which must be included. Is that something you tried?
Code: Select all
YYYY-MM-DD HH24:MI:SS.TTT
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Code: Select all
Oconv(InLink.Transaction_Date, "D-YMD[4,2,2]") : " 00:00:00.000"
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Re: Load date into SQL Server - datetime datatype)
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.mickboland wrote: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
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
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
LUK
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.
-
- Participant
- Posts: 4
- Joined: Tue Jun 28, 2005 5:03 pm
- Location: Brisbane, Australia
Re: Load date into SQL Server - datetime datatype)
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.
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.