Sending Datetime or Datetime2 values via linked server in SQL Server - rounded to millisecond

kamil_kosno

Hello, I'm using SQL Server with linked server pointing to Vertica database. I have Datetime2(6) values in a table and when trying to select and insert to Vertica (target field defined as TIMESTAMP(6)), the value is rounded to the closest millisecond and the last 3 digits are always 0s. On the other hand, when reading values from Vertica TIMESTAMP(6) field I get correct precision. Is there a way to solve this issue?

SruthiA

Could you please share sample data and results?

kamil_kosno

sample data: 2023-10-15 08:54:49.1966480 (source field in SQL Server DATETIME2(7)
result: 2023-10-15 08:54:49.196000 (target field in Vertica TIMESTAMP6)

I suspect it has something to do with MSDASQL provider, because I've tried casting to DATETIME and even a literal character string (e.g. 2023-10-15 08:54:49.196648) and it always ignores anything outside milliseconds.

Here is how linked server was defined and data inserted (the names are not real):
`EXEC master.dbo.sp_addlinkedserver @server = N'XXX’, @srvproduct=N'Vertica', @provider=N'MSDASQL', @datasrc=N'XXX', @catalog=N'YYY'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'XXX',@useself=N'False',@locallogin=NULL,@rmtuser=N'XXX_YYY',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

DECLARE @runDate DATETIME2(7) = SYSDATETIME();

INSERT INTO OPENQUERY (

    XXX

    ,'SELECT val from DB.SCHEMA.TableName'

    )

SELECT @runDate;`

SruthiA

Since I cannot use MSDASQL, I tested it by creating another table and using insert select.. I don't see any truncation. I tried copy from stdin as well.. it works fine

eonv234=> create table test_ts(a TIMESTAMP(6));
CREATE TABLE
eonv234=> insert into test_ts values('2023-10-15 08:54:49.1966480');

OUTPUT

  1

(1 row)

eonv234=> commit;
COMMIT
eonv234=> select * from test_ts;

a

2023-10-15 08:54:49.196648
(1 row)

eonv234=> create table test_ts_copy(a TIMESTAMP(6));
CREATE TABLE

eonv234=> insert into test_ts_copy select * from test_ts;

OUTPUT

  1

(1 row)

eonv234=> commit;
COMMIT
eonv234=> select * from test_ts_copy ;

a

2023-10-15 08:54:49.196648
(1 row)

eonv234=> copy test_ts from stdin
eonv234-> ;
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.

2023-10-15 08:54:49.1966480
.

eonv234=> select * from test_ts;

a

2023-10-15 08:54:49.196648
2023-10-15 08:54:49.196648
(2 rows)

eonv234=>