Thank you

Hi Yasir,

It seems to be a precision issue. If you Insert the datetime variable into the table again using the insert statement, you will see that the milliseconds are not missing.

This is a legacy problem, which also exists in PB 12.6.

Please open a support ticket in the support portal and please provide a simple PB case & the detailed steps to reproduce the issue, Many thanks in advance.
https://www.appeon.com/standardsupport/

Best Regards,

Peter

Yasir -

Since you have not gone into any details how you are querying the date/time value from SQL Server 2019 and retrieving the value into PB, I have performed some experiments using PB 2017 R3 Build 1915 and SQL Server 2019 via the SNC (OLE DB) native interface.

Below is the SQL SELECT for a test DataWindow that returns a datetime2 value:

If the SELECT statement uses GETDATE() instead of SYSDATETIME(), a datetime value is returned. The time portion of a SQL Server datetime has a resolution of milliseconds (hh:mm:ss.fff) and a datetime2 value provides seven decimal places.

Below is the preview of this DataWindow in the DW Painter:

A time value in PB contains at most six decimal places, so PB drops the seventh decimal place from the datetime2 value supplied by SQL Server.

*** Note ***

I had to alter the default display format for this column object in the DW Painter in order to see the fractional seconds. Normally, the default display format supplied by PB does not include fractional seconds:

If a datetime value is returned in the SELECT statement, only three decimal places are returned:

You can also put a datetime or datetime2 value into a PB datetime variable via inline SQL:

Datetime ldt_now

SELECT GETDATE() 
  INTO :ldt_now 
  FROM (SELECT 1 AS [a_column] AS [dummy_table])
USING SQLCA;

I'm not sure why you think PB is not able to obtain the fractional seconds from SQL Server, but I'm guessing it's because you do not see the fractional seconds displayed in the PB debugger or in a DW column because of the display format:

Remember I stated earlier that PB uses a default display format that omits the fractional seconds... The fractional seconds ARE there:

I hope this helps to resolve your question.

Best regards, John

I don't want to convert the value to a string.

I am using the datetime value in an Embedded SQL

When converting a DateTime to String or when displaying in a DataWindow, you need to format it with .fff on the end like so:

ls_date = String(ldt_thedate, "mm/dd/yyyy hh:mm:ss.fff")

Here is the connection string from my INI file

DBParm="PROVIDER='SQLOLEDB',DATASOURCE='YASIRMLPTP1',PROVIDERSTRING='database=DB_NAME',PBTrimCharColumns='YES',INTEGRATEDSECURITY='SSPI',OJSyntax='PB',appname='YM',StaticBind=0"

Hi Yasir;

  What DB client are you using to connect to SS!

Ie: SQLCA = "???"

Regards ... Chris