6-digit milliseconds on insert: quick fix on the server side?

Hi,

Powerbuilder 2019 R3

MS SQL Server

INI file excerpt:

[SQLServer]
LogID=<SQLuser>
Logpass=<SQLpassword>

[Database]
DBMS=SNC SQL Native Client(OLE DB)
Database=<DBname>
ServerName=<SQL server instance>
DBParm=Provider='SQLNCLI10',Database='<DBname>',DBTextLimit='32766', ProviderString='OLE DB Services=-4; MARS Connection=False', Identity='SCOPE_IDENTITY()', TRIMSPACES=1, RecheckRows=1

We have run into instances in which PB and/or SQL Native Client tries to insert a row into a table - the record includes a datetime column, that for some reason sets the datetime with a 6-digit millisecond. (EX: insert into mytable (col1, col2) values (<ID value>, '2022-06-23 12:34:56:357777').

If the datetime only has 3 digit milliseconds, no problem.

We could write a PB fix to truncate the milliseconds down to 3 digits, but that would require compiling the app, testing, delivering, etc. which would take weeks. To save time, is there a setting within MS SQL Server to automatically truncate the milliseconds as we wish?

Thanks in advance,
Scott

Comment

Miguel Leeuwe
Friday, 24 June 2022 04:51 AM UTC

In Oracle you can do an ALTER SESSION to set the timstamp and date(time) precision for milliseconds. I don't know if there's something similar for sql server.

Helpful 0

Scott Gorke
Wednesday, 17 August 2022 14:15 PM UTC

Thanks Miguel. The SQLCA DBParm solution was considered, but because a client was complaining about the issue, we wrote a fix in the Powerbuilder code to truncate the milliseconds to three digits. For the unhappy client, this was the fastest fix (our app already trims to 3-digit milliseconds in several other areas, and our client found 2 other areas that we missed.).

There are no comments made yet.

Resolved 6-digit milliseconds on insert: quick fix on the server side?