Repository: microsoft/go-mssqldb
Pull request #242 (2025-03-09 17:35:17)

DATETIME: fix 1/300 of a seconds rounding logic (Bulk Copy related)
This PR fixes an issue: https://github.com/microsoft/go-mssqldb/issues/181

codecov-commenter: ## [Codecov](https://app.codecov.io/gh/microsoft/go-mssqldb/pull/242?dropdown=coverage&src=pr&el=h1&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=microsoft) Report
All modified and coverable lines are covered by tests :white_check_mark:
> Project coverage is 74.95%. Comparing base [(`d27f997`)](https://app.codecov.io/gh/microsoft/go-mssqldb/commit/d27f99731b220acf4e5efa076960154603e90b60?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=microsoft) to head [(`3690478`)](https://app.codecov.io/gh/microsoft/go-mssqldb/commit/369047864f50e89eee5bf7cb5825acd8447fa802?dropdown=coverage&el=desc&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=microsoft).

<details><summary>Additional details and impacted files</summary>


```diff
@@            Coverage Diff             @@
##             main     #242      +/-   ##
==========================================
+ Coverage   74.91%   74.95%   +0.04%     
==========================================
  Files          32       32              
  Lines        6457     6461       +4     
==========================================
+ Hits         4837     4843       +6     
+ Misses       1332     1329       -3     
- Partials      288      289       +1     
```

</details>

[:umbrella: View full report in Codecov by Sentry](https://app.codecov.io/gh/microsoft/go-mssqldb/pull/242?dropdown=coverage&src=pr&el=continue&utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=microsoft).   
:loudspeaker: Have feedback on the report? [Share it here](https://about.codecov.io/codecov-pr-comment-feedback/?utm_medium=referral&utm_source=github&utm_content=comment&utm_campaign=pr+comments&utm_term=microsoft).

<details><summary>🚀 New features to boost your workflow: </summary>

- ❄ [Test Analytics](https://docs.codecov.com/docs/test-analytics): Detect flaky tests, report on failures, and find test suite problems.
</details>

Patch:
diff --git a/types.go b/types.go
index 94b202f..8e5c7dc 100644
--- a/types.go
+++ b/types.go
@@ -296,7 +296,7 @@ func encodeDateTime(t time.Time) (res []byte) {
 	basedays := gregorianDays(1900, 1)
 	// days since Jan 1st 1900 (same TZ as t)
 	days := gregorianDays(t.Year(), t.YearDay()) - basedays
-	tm := 300*(t.Second()+t.Minute()*60+t.Hour()*60*60) + t.Nanosecond()*300/1e9
+	tm := 300*(t.Second()+t.Minute()*60+t.Hour()*60*60) + nanosToThreeHundredthsOfASecond(t.Nanosecond())
 	// minimum and maximum possible
 	mindays := gregorianDays(1753, 1) - basedays
 	maxdays := gregorianDays(9999, 365) - basedays
@@ -317,12 +317,20 @@ func encodeDateTime(t time.Time) (res []byte) {
 func decodeDateTime(buf []byte) time.Time {
 	days := int32(binary.LittleEndian.Uint32(buf))
 	tm := binary.LittleEndian.Uint32(buf[4:])
-	ns := int(math.Trunc(float64(tm%300)/0.3+0.5)) * 1000000
+	ns := threeHundredthsOfASecondToNanos(int(tm % 300))
 	secs := int(tm / 300)
 	return time.Date(1900, 1, 1+int(days),
 		0, 0, secs, ns, time.UTC)
 }
 
+func threeHundredthsOfASecondToNanos(ths int) int {
+	return int(math.Trunc(float64(ths)/0.3+0.5)) * 1000000
+}
+
+func nanosToThreeHundredthsOfASecond(ns int) int {
+	return int(math.Round(float64(ns) * 3 / 1e7))
+}
+
 func readFixedType(ti *typeInfo, r *tdsBuffer, c *cryptoMetadata) interface{} {
 	r.ReadFull(ti.Buffer)
 	buf := ti.Buffer
diff --git a/uniqueidentifier_null.go b/uniqueidentifier_null.go
index a9c4ba4..260d693 100644
--- a/uniqueidentifier_null.go
+++ b/uniqueidentifier_null.go
@@ -2,6 +2,7 @@ package mssql
 
 import (
 	"database/sql/driver"
+	"encoding/json"
 )
 
 type NullUniqueIdentifier struct {
@@ -63,3 +64,10 @@ func (n *NullUniqueIdentifier) UnmarshalJSON(b []byte) error {
 	}
 	return err
 }
+
+func (n NullUniqueIdentifier) MarshalJSON() ([]byte, error) {
+	if !n.Valid {
+		return []byte("null"), nil
+	}
+	return json.Marshal(n.UUID)
+}


Test patch:
diff --git a/types_date_and_time_test.go b/types_date_and_time_test.go
new file mode 100644
index 0000000..71007d1
--- /dev/null
+++ b/types_date_and_time_test.go
@@ -0,0 +1,150 @@
+package mssql
+
+import (
+	"context"
+	"fmt"
+	"testing"
+	"time"
+)
+
+// TestDatetimeAccuracy validates that DATETIME type values
+// are properly created from time.Time giving the same rounding
+// logic as SQL Server itself implements.
+//
+// Datetime is rounded to increments of .000, .003, or .007 seconds (accuracy is 1/300 of a second)
+// (see: https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver16).
+//
+// This test creates 3 schematically identical tables filled in 3 different ways:
+//
+//   - datetime_test_insert_time_as_str (filled via regular INSERT with time as str params)
+//   - datetime_test_insert_time_as_time (filled via regular INSERT with time as go time.Time params)
+//   - datetime_test_insert_bulk (filled via Bulk Copy)
+//
+// After creating these 3 tables the test will compare the data read from all of the tables
+// expecting the data to be identical.
+func TestDatetimeAccuracy(t *testing.T) {
+	ctx := context.Background()
+	conn, logger := open(t)
+	t.Cleanup(func() {
+		conn.Close()
+		logger.StopLogging()
+	})
+
+	createTable := func(tableName string) {
+		_, err := conn.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s", tableName))
+		if err != nil {
+			t.Fatal("Failed to drop table: ", err)
+		}
+		_, err = conn.Exec(fmt.Sprintf(`CREATE TABLE %s (
+			id INT NOT NULL PRIMARY KEY,
+			dt DATETIME
+		)`, tableName))
+		if err != nil {
+			t.Fatal("Failed to create table: ", err)
+		}
+		t.Cleanup(func() { _, _ = conn.Exec(fmt.Sprintf("DROP TABLE IF EXISTS %s", tableName)) })
+	}
+
+	fillTable := func(tableName string, dts []any) {
+		for i, dt := range dts {
+			_, err := conn.Exec(fmt.Sprintf("INSERT INTO %s (id, dt) VALUES (@p1, @p2)", tableName), i, dt)
+			if err != nil {
+				t.Fatal(err)
+			}
+		}
+	}
+
+	fillTableBulkCopy := func(tableName string, dts []any) {
+		conn, err := conn.Conn(context.Background())
+		if err != nil {
+			t.Fatal(err)
+		}
+		defer func() { _ = conn.Close() }()
+		stmt, err := conn.PrepareContext(ctx, CopyIn(tableName, BulkOptions{}, "id", "dt"))
+		if err != nil {
+			t.Fatal(err)
+		}
+		for i, dt := range dts {
+			if _, err = stmt.Exec(i, dt); err != nil {
+				t.Fatal(err)
+			}
+		}
+		if _, err = stmt.Exec(); err != nil {
+			t.Fatal(err)
+		}
+	}
+
+	readTable := func(tableName string) (res []time.Time) {
+		rows, err := conn.QueryContext(ctx, fmt.Sprintf("SELECT dt FROM %s ORDER BY id", tableName))
+		if err != nil {
+			t.Fatal(err)
+		}
+		defer func() { _ = rows.Close() }()
+		for rows.Next() {
+			var dt time.Time
+			if err = rows.Scan(&dt); err != nil {
+				t.Fatal(err)
+			}
+			res = append(res, dt)
+		}
+		if rows.Err() != nil {
+			t.Fatal(rows.Err())
+		}
+		return res
+	}
+
+	// generate data to be inserted into the tables:
+	// times with fraction of a second from .000 to .999.
+	var dtsTime []any
+	var dtsStrs []any
+	for i := 0; i < 1000; i++ {
+		ns := int(time.Duration(i) * (time.Second / 1000) * time.Nanosecond)
+		dt := time.Date(2025, 4, 11, 10, 30, 42, ns, time.UTC)
+		str := dt.Format("2006-01-02T15:04:05.999Z")
+		dtsTime = append(dtsTime, dt)
+		dtsStrs = append(dtsStrs, str)
+	}
+
+	createTable("datetime_test_insert_time_as_str")
+	fillTable("datetime_test_insert_time_as_str", dtsStrs)
+
+	createTable("datetime_test_insert_time_as_time")
+	fillTable("datetime_test_insert_time_as_time", dtsTime)
+
+	createTable("datetime_test_insert_bulk")
+	fillTableBulkCopy("datetime_test_insert_bulk", dtsTime)
+
+	as := readTable("datetime_test_insert_time_as_str")
+	bs := readTable("datetime_test_insert_time_as_time")
+	cs := readTable("datetime_test_insert_bulk")
+
+	if len(dtsTime) != len(as) || len(dtsTime) != len(bs) || len(dtsTime) != len(cs) {
+		t.Fatalf("Not all data inserted into tables: want = %d, got = %d %d %d", len(dtsTime), len(as), len(bs), len(cs))
+	}
+
+	for i := 0; i < len(dtsTime); i++ {
+		if !as[i].Equal(bs[i]) || !as[i].Equal(cs[i]) {
+			t.Fatalf(`Rows not equal at #%d:
+			| %-36s | %-36s | %-36s |
+			| %36s | %36s | %36s |`,
+				i,
+				"datetime_test_insert_time_as_str",
+				"datetime_test_insert_time_as_time",
+				"datetime_test_insert_bulk",
+				as[i].Format(time.RFC3339Nano),
+				bs[i].Format(time.RFC3339Nano),
+				cs[i].Format(time.RFC3339Nano),
+			)
+		}
+	}
+}
+
+func TestThreeHundredthsToNanosConverter(t *testing.T) {
+	for want := 0; want < 300; want++ {
+		ns := threeHundredthsOfASecondToNanos(want)
+		got := nanosToThreeHundredthsOfASecond(ns)
+		if want != got {
+			t.Fatalf("want = %d, got = %d", want, got)
+		}
+	}
+}
diff --git a/uniqueidentifier_null_test.go b/uniqueidentifier_null_test.go
index dc29276..8897281 100644
--- a/uniqueidentifier_null_test.go
+++ b/uniqueidentifier_null_test.go
@@ -4,6 +4,7 @@ import (
 	"bytes"
 	"database/sql"
 	"database/sql/driver"
+	"encoding/json"
 	"fmt"
 	"reflect"
 	"testing"
@@ -210,6 +211,42 @@ func TestNullableUniqueIdentifierUnmarshalJSONNull(t *testing.T) {
 	}
 }
 
-var _ fmt.Stringer = NullUniqueIdentifier{}
-var _ sql.Scanner = &NullUniqueIdentifier{}
-var _ driver.Valuer = NullUniqueIdentifier{}
+func TestNullableUniqueIdentifierMarshalJSONNull(t *testing.T) {
+	t.Parallel()
+	nullUUID := NullUniqueIdentifier{
+		UUID:  [16]byte{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0},
+		Valid: false,
+	}
+
+	got, err := nullUUID.MarshalJSON()
+	if err != nil {
+		t.Fatal(err)
+	}
+	want := []byte{0x6e, 0x75, 0x6c, 0x6c} // null = %x6e.75.6c.6c
+	if !reflect.DeepEqual(got, want) {
+		t.Errorf("got %v; want %v", got, want)
+	}
+}
+
+func TestNullableUniqueIdentifierJSONMarshalNull(t *testing.T) {
+	t.Parallel()
+	nullUUID := NullUniqueIdentifier{
+		UUID:  [16]byte{0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0, 0x0},
+		Valid: false,
+	}
+
+	got, err := json.Marshal(nullUUID)
+	if err != nil {
+		t.Fatal(err)
+	}
+	want := []byte{0x6e, 0x75, 0x6c, 0x6c}
+	if !reflect.DeepEqual(got, want) {
+		t.Errorf("got %v; want %v", got, want)
+	}
+}
+
+var (
+	_ fmt.Stringer  = NullUniqueIdentifier{}
+	_ sql.Scanner   = &NullUniqueIdentifier{}
+	_ driver.Valuer = NullUniqueIdentifier{}
+)