topic Re: SQL Server DATETIME2 truncating to DATETIME in Talend Studio

SQL Server DATETIME2 truncating to DATETIME

TwoStepSRP — Mon, 24 Apr 2017 21:50:26 GMT

I have SQL Server input and output components with a tmap in between to manipulate a few columns. I have a DATETIME2 column that translates exactly from input to output, however the data in the resulting table is in DATETIME format rather than DATETIME2 - i.e. it lost the last 3 decimal digits, retaining only 3 (from 0.111111 to 0.111). The Schema of both SQL Server components shows the columns as DATETIME2 and I specified a formal of "yyyy-MM-dd HH:mm:ss.SSSSSS", though the patterns seem to indicate it only supports 3 SSS values. I'm not sure if the last digits are gettting lost when it is pulled from SQL Server of in the tmap itself. Is this expected behaviour?

If I change the input component - which is a select from a view - to have the field be a varchar containing a string in the format of "yyyy-MM-dd HH:mm:ss.SSSSSS", is there a format or function that can be applied in tmap to change it back to DATETIME2 and thus write it correctly to SQL Server?

Thanks,

-Sheila

Re: SQL Server DATETIME2 truncating to DATETIME

Anonymous — Tue, 25 Apr 2017 03:43:22 GMT

Hi,

Java don't support for six but only for the millisecond sss.
If you want to show six, write .SSSSSS and the other part will be Auto completed with "000".

Please take a look at this article:https://docs.oracle.com/javase/tutorial/i18n/format/simpleDateFormat.html

Best regards

Sabrina

Re: SQL Server DATETIME2 truncating to DATETIME

TwoStepSRP — Tue, 25 Apr 2017 13:55:50 GMT

Thanks. I'll write a SQL Server procedure to properly push the data, since I need to retain all 6 of the incoming decimal digits.

-Sheila

Re: SQL Server DATETIME2 truncating to DATETIME

Anonymous — Wed, 26 Apr 2017 08:21:31 GMT

Hi,

Feel free to let us know if there is any further help we can give.

Best regards

Sabrina