Datetime2 data type

Datetime2 data type

  
Hi there!

I'm having an issue related to the SQL data type datetime2 that I need to use and import from an external system. With Integration Studio, I've imported this entity where its attribute is converted to Text(27), but I need to have a datetime. I've made an application test. It works well with this data type in SQL, though it's a Datetime in Service Studio. Reading this attribute to a table records works fine, saving it to an entity created in Service Studio works fine also. The precision is not lost, but it's rounded. If I do a join with the attribute of the new created entity with the attribute of the imported external entity, there's no result since the precision is different.

I have to have this attribute with this precision and with this data type, since using a text, I'll have performance issues. We're talking about a large BD where some tables are partitioned with attributes of datetime data type. What can I do about this? Any help?

Thanks in advance,
Tiago

Hi Tiago,
I am wondering if you are using the complete datetime expression when it is converted for a text variable/attribute?
You can add differente parts of a text expression to fill a datetime attribute with a string builder or similar tool and with that I guess you will not loose your precision.
As an example (VB.NET) you can grab this text expression and pass it to a datetime like this:
"2014-04-02 18:05:03.123"
mydatetimevar.year = left(mytextvar,4)
mydatetimevar.month = mid(mytextvar,6,2)
...
mydatetimevar.milisenconds = right(mytextvar,3)

Hope this helps you :-)

Hi João,

Thanks for your answer, but I think you missed the most important part of the question that I presented. I need to have the data type datetime2 as a valid data type in SQL and in the Outsystems Platform in order to work with it. My problem is not present the values converted to text in a web screen. My problem is that the precision is lost, since the Service Studio only supports the data type Date Time. As an example, watch the picture attached. Service Studio converts the data type from Datetime2 changed directly in SQL to Datetime when the application is published and then the precision is lost, since the ms are rounded.

Tiago


Sorry for my misinterpretation of your question.
But I think one possible solution can be something as I'd showed you.
You just need to parse the first 3 characters of the milisenconds datetime2 value.
If you can handle this value in your extension, you need to convert this (datetime2) value to a text string and then build your datetime value based on text parses of your datetime2 expression.
Doing this you'll never round the milisecond value. But this is just a workaround...