Hi All,
I'm trying to insert data in to SQL server 2008 table. The data type is Datetime2(7) and I tried all the combinations like using functions like below in transformer
StringToTimestamp(TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss"):'.000000',"%yyyy-%mm-%dd %hh:%nn:%ss.6")
In DRS stage I made SQL type as Timestamp and extended as "microseconds" and in length I mentioned as 26 and scale as 6
I'm getting warning's as
Metadata mismatch for column 'CRETD_TP': Datastage column type and database column type differ.
Metadata mismatch for column 'CRETD_TP': PRECISION Expected = 20, Actual = 27
Metadata mismatch for column 'CRETD_TP': SCALE Expected = 3, Actual = 0
I also tried
StringToTimestamp(TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss"):'.000',"%yyyy-%mm-%dd %hh:%nn:%ss.3")
In DRS stage I made SQL type as Timestamp and extended as "microseconds" and in length I mentioned as 23 and scale as 3
I'm getting warning's as
Metadata mismatch for column 'CRETD_TP': Datastage column type and database column type differ.
Metadata mismatch for column 'CRETD_TP': PRECISION Expected = 20, Actual = 27
Metadata mismatch for column 'CRETD_TP': SCALE Expected = 3, Actual = 0
And finally changed DRS stage I made SQL type as Varchar(27)
TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss") and it is working fine but I'm still getting a single warning
Metadata mismatch for column 'CRETD_TP': Datastage column type and database column type differ
Is there any way I can supress the above warning with out using message handler or any work around to solve the aboe issue
Thanks,
Raju
Datetime2(7) issue in SQl server 2008
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Could this data type require FOUR fractional second digits (making a total of 27 characters in the timestamp)? You'd need "%ss.4" in the format string as well as only four zeroes in the constructed data.
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: Datetime2(7) issue in SQl server 2008
Datetime2(7) is also timestamp field. In SQL server datetime wont store the data if the data (year) is prior to 1753. On that case need to use datetime2.
You can choose the timestamp as the datatype in DS and load the data. It will work.
You are trying to insert the data to datetime2. But how the source data looks like?
If you want to load the current time , then you can choose the datatype as timestamp for the column in the TFM without any conversion and pass it.
DS User
You can choose the timestamp as the datatype in DS and load the data. It will work.
You are trying to insert the data to datetime2. But how the source data looks like?
If you want to load the current time , then you can choose the datatype as timestamp for the column in the TFM without any conversion and pass it.
DS User
Hi All,
Thanks everyone for the help. Timestamp datatype is working fine if I use ODBC stage,
For DRS stage I need to use NVARCHAR(27) datatype and I need to use the TimestampToString function like below
TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss")
So when we are using a DRS stage to load data in to Sql server we need to us NVARCHAR datatype for Date, Datetime2(7) datatypes in SQL server 2008
Thanks,
Raju
Thanks everyone for the help. Timestamp datatype is working fine if I use ODBC stage,
For DRS stage I need to use NVARCHAR(27) datatype and I need to use the TimestampToString function like below
TimestampToString(CurrentTimestamp(),"%yyyy-%mm-%dd %hh:%nn:%ss")
So when we are using a DRS stage to load data in to Sql server we need to us NVARCHAR datatype for Date, Datetime2(7) datatypes in SQL server 2008
Thanks,
Raju
somaraju