Inserting fractional seconds into datetime2 columns

[jamesetaylor-tudor]

Hi there, I'm having issues providing a fractional component to a timestamp when inserting into a column of type datetime2. If I specify fraction as anything other than 0, I get error 22008 (fractional second precision exceeds the scale specified in the parameter binding). Should I be doing something different to insert this kind of data?

Microsoft SQL Server 2022 (RTM-CU9) (KB5030731) - 16.0.4085.2 (X64) Sep 27 2023 12:05:43 Copyright (C) 2022 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 20.04.6 LTS) <X64>

`use std::collections::HashMap;

use odbc_api::buffers::BufferDesc;
use odbc_api::*;

fn main() {
let env = Environment::new().unwrap();

let odbc_settings = HashMap::from([
    ("DRIVER", "/usr/lib64/libmsodbcsql-17.so"),
    ("SERVER", "xxxxx"),
    ("UID", "xxxxx"),
    ("PWD", "xxxxxxx"),
]);

let conn = env
    .connect_with_connection_string(
        &odbc_settings
            .iter()
            .map(|(&k, &v)| format!("{k}={{{v}}}"))
            .collect::<Vec<_>>()
            .join(";"),
        ConnectionOptions {
            login_timeout_sec: None,
        },
    )
    .expect("can't connect");

conn.execute("drop table tbl", ()).ok();
conn.execute("create table tbl(ts datetime2 not null)", ())
    .expect("can't create a table");

let mut inserter = conn
    .prepare("insert into tbl(ts) values (?)")
    .expect("can't create insert stmt")
    .into_column_inserter(1, [BufferDesc::Timestamp { nullable: false }])
    .expect("can't create inserter");

inserter.set_num_rows(1);

inserter
    .column_mut(0)
    .as_slice::<odbc_api::sys::Timestamp>()
    .unwrap()[0] = odbc_api::sys::Timestamp {
    year: 1970,
    month: 1,
    day: 1,
    hour: 0,
    minute: 0,
    second: 0,
    fraction: 1,
};

inserter.execute().expect("couldn't insert");

}
`

[pacman82]

Hello @jamesetaylor-tudor ,

argument is bound with percision 7. This is also the maximum supported precision for datetime2 in an MSSQL Database. The timestamp fraction field is specified in nanoseconds (precision 9). So whatever number you put in there, at least its last two digits shoud end in 0. Fraction 1 would mean 1 nanosecond and not e.g. one tenth of a second.

Best, Markus

[jamesetaylor-tudor]

thank you!

[pacman82]

You are welcome

[pacman82]

Here is how arrow-odbc generates timestamps: https://github.com/pacman82/arrow-odbc/blob/3b3288d8899b51e0efe401a0271c5244ccca73a2/src/date_time.rs#L77