Loading Timestamp To SQL Server 2005 datetime

craigbarrick · Posted: Fri Jan 23, 2009 4:19 am

Apologies if already answered but I couldn't find it anywhere.

I have an issue whereby that I am trying to load from a dataset to a SQL Server 2005 table using the ODBC Connector.

All works fine until I try and load a timestamp (26,6) into a SQL datetime (23,3) - I then get an Arithmetic Overflow abort due to SQL expecting milliseconds (I think!).

In the Enterprise ODBC stage there are no such problems and Im a happy man as all loads as expected and the timestamp issues do not occur.

Would like to know if anyone encountered this problem / has advice.

Thanks
Craig

ray.wurlod · Posted: Fri Jan 23, 2009 2:59 pm

You may need to apply a conversion, changing the fractional part of the seconds from .6 to .3 in the timestamp format picture. This may be possible in a Modify stage (I've not tried that). It is def ...

What's this?

shamshad · Posted: Fri Jan 23, 2009 4:02 pm

May be worth trying this

(1) First HardCode Timestamp value and insert in target table and see if
jobs runs successfully.
(2) If it works fine, treat your input timestamp as character column and
then format the input value in the same way as the hardcoded value.

mk_ds09 · Posted: Mon Jan 26, 2009 3:17 am

Hi..

The default format of the timestamp is as following :-
%yyyy-%mm-%dd%hh:%nn:%ss. onent.

And seconds are stored as follows :-
– %ss.n: A two-digit seconds plus fractional part, where n is the
number of fractional digits with a maximum value of 6. If n is 0,
no decimal point is stored..

If your datatype is (26,3), you need to change the fractional part accordingly to store those values.

Hope this helps..

-------------------------
MK

craigbarrick · Posted: Thu Jan 29, 2009 4:37 am

Thanks guys.

Hardcoding the date led me to discover that the date format expected at the SQL Server target was yyyy-dd-mm and not yyyy-mm-dd as per the dataset and this is what was causing the arithmetic overflow (not the microseconds as first thought).

Just find it annoying that the Enetrprise ODBC handles this conversion perfectly but the ODBC Connector requires extra effort in handling this data type - or a change to the set up of the target database structure suffices (which fortunately I could do).

The requirement for this approach has since now gone away for our project anyway as the client is now looking to use DB2 but I think the fact they dont have NLS installed may have contributed to this problem and I believe that NLS may resolve some of this.
If I ever get chance I will try and re-post findings.

Thanks

ray.wurlod · Posted: Thu Jan 29, 2009 3:16 pm

Connectors are new technology. I'd report this as a bug in the connector. After all, YYYY-DD-MM does not conform to any industry standard (particularly not ODBC), and IBM prides itself on standards ...

What's this?