Skip to main content
www.usoft.com To www.usoft.com

We’re working on a product where timing is quite important. We therefor would like to store a Datetime stamp up to a thousands of a second. I’ve tried multiple solutions manipulating the IO-format of the domain I’m storing the datetimestamp in unfortunately without any luck. The IO formats I tried so far are: 

  • YYYY-MM-DD HH24:MI:SS.FF3
  • YYYY-MM-DD HH24:MI:SS.NNN
  • YYYY-MM-DD HH24:MI:SS.SSS

they each store the datetime stamps without the milliseconds.

Even when manually inserting the date via a query in USoft Application, it inserts it without the milliseconds, also shown below

 

 

 

Running that exact query directly on the database, and the milliseconds get inserted

 

 

 

Is there an easy way to work with milliseconds?

Hi Dennis,

Thanks for asking your question.

Unfortunately, USoft at this moment does not support working with milliseconds. 

The enhancement request to be able to do so has been made but has not yet been scheduled.

Hi Dennis,

As stated before by our Customer service desk, USoft does not yet support data types like datetime2 and datetimeoffset. Yet you can retrieve milliseconds using RDMI and store them in a column of data type varchar(24) or nvarchar(24).

From the description of your issue I assume humans will not change the timestamp. They will only want to look at it (and maybe use it for sorting). That would be a prerequisite to make the workaround below reliable.

In the USoft Definer:
- Create a domain with data type nvarchar(24)
    Do not(!) add an IO format to the domain.
- Create a domain constraint that fills columns based upon the domain with:

    convert( nvarchar(24), rdbms.getutcdate(), 127 ) || 'Z'

e.g. when the column is empty (see image).

If you define columns on this domain, inserting rows will give you a string like the output below:
    
    2022-11-09T14:16:49.307Z

The thought behind adding the "Z" is that it makes presenting the timestamps in any timezone easier. Most programming languages have functions to  convert the above string into a date with time.

(If you want local timestamps, you could use:
    convert( nvarchar(24), rdbms.getdate(), 127 )
If you want nanoseconds in your timestamps, you would need a bigger column definition to store the result of:
    convert( nvarchar(27), rdbms.SYSUTCDATETIME(), 127 )
)

As an aside, after I conjured up this workaround, I saw that Microsoft describe this approach for what they call "Down-level Clients"
https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime2-transact-sql?view=sql-server-ver16

"Backward Compatibility for Down-level Clients

Some down-level clients do not support the time, date, datetime2 and datetimeoffset data types. The following table shows the type mapping between an up-level instance of SQL Server and down-level clients.
..."


Regards,

Robert

Reply


Terms & ConditionsCookie settings