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?
Question
Sending Datetime or Datetime2 values via linked server in SQL Server - rounded to millisecond
+1Could you please share sample data and results?
+1Since 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=>
Sign up
Already have an account? Login
Welcome to the Rocket Forum!
Please log in or register:
Employee Login | Registration Member Login | RegistrationEnter your E-mail address. We'll send you an e-mail with instructions to reset your password.
77 4th Avenue
Waltham, MA 02451 USA
