Where are the milliseconds coming from?

Where are the milliseconds coming from?

Hi community,
I’m getting a really strange problem when trying to pass a datetime value into an advquery.
Basically, the variable value that is passed to the advquery is going to the SQL Server with milliseconds when in the Outsystems platform everything is in seconds . As such, where is the platform getting the milliseconds value from?
Here is the query from the SQL profiler (not original but for example)
exec sp_executesql N'
                (select 1
                                               "MyDB"."DBO"."OSUSR_VWT_EMPRESA".[Id] =  "MyDB"."DBO"."OSUSR_VWT_MYTABLE".[IDEMPRESA]              
                                                "MyDB"."DBO"."OSUSR_VWT_EMPRESA".[CriadoEm] = @qpDataReferencia)'
                               ,N'@qpDataReferencia datetime',@qpDataReferencia='2014-07-15 18:37:46.537'
Just a note, the @qpDataReferencia is a local variable that is loaded from a table where the values are created using a SSIS process. 

Does anyone have this problem?
Helio -

It all depends on what you are passing into that parameter. My impression is that the milliseconds are not exposed to OutSystems, but they lurk under the hood.

External SQL programs like SSIS are independent from OS specifications so they can have full precision. Even inside OS I would say a run time value like CurrDateTime() is probably more precise than a stored DateTime.So yes, I'd say SQL Profiler can show values different from the ones you see in the platform.
Hi Justin and Nuno, thanks for the replies but I still can't understand what is happening under the hood because. I created a simple example to exemplify the code.
It's as if the platform stored the variable value in the SQL server somewhere and then uses it in the advqueries without the value comming back to the FE server.
BTW, everything works when I test it in the service studio, but not in runtime.
Helio -

You still haven't explained where the value that you are passing into the parameter is coming from. That is a key piece of information to understanding this.

Hi Justin,
the value is coming from the table “Execution” a stored in the variable “DataReferencia” (As seen in the example.png) and the records are inserted in the table “Execution” using a SSIS process with the following command
INSERT INTO [OSUSR_EVO_Execution] ([DateRef], [Status]) VALUES (GETDATE(),'C')
There it is. If SSIS fills the table, it is not truncating the milisseconds.

OutSystems was made to work with OS formated data, not SQL data. Service Studio probably is better prepared for those situations.

Can't your SSIS truncate the time?
Nuno's right, that's the exact issue. If you were using CurrDateTime() to populate it, you would see that the milliseconds are 0.

I had the reverse issue a few months ago, I thought it would store milliseconds with CurrDateTime() and I was sorting on that expecting millisecond precision and it caused big problems as a result!

So if I understand correctly what you guys are saying, when I get the value from the database, the datetime comes with milliseconds even though you can’t see it on the screen or anywhere else in the OS platform, you can only see it on the database.

I’ve been banging my head against the wall lately and I don’t think this is as linear as this. I can’t be sure of this but I think there is a different behavior to this either  you are doing a select or an insert using and advquery.

This issue is a real pain because I always thought that when you do a select from the DB of a datetime field to the OS platform, the precision would always be in seconds and not in milliseconds.

It would be great if someone from Outsystems could confirm this theory.

Many thanks Nuno and Justin
Helio -

Yes, that is correct. the OutSystems DateTime type does not support milliseconds, so they are not exposed to the application, but if you use Advance Queries, you will see and be able to access/set milliseconds.