Four Stars

SQL Server DATETIME2 truncating to DATETIME

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

  • Data Integration
1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

Re: SQL Server DATETIME2 truncating to DATETIME

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
3 REPLIES
Moderator

Re: SQL Server DATETIME2 truncating to DATETIME

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

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: SQL Server DATETIME2 truncating to DATETIME

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

Moderator

Re: SQL Server DATETIME2 truncating to DATETIME

Hi,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.