search

microsoft / go-mssqldb

Microsoft SQL server driver written in go language
BSD 3-Clause "New" or "Revised" License
262 stars 53 forks source link

DATETIME rounding does not match SQL Server documentation #181

Open mmulvanny opened 3 months ago

mmulvanny commented 3 months ago

Describe the bug go-mssqldb's rounding behavior for DATETIME types does not match Microsoft's table or the behavior of SQL Server's CAST.

The program below compares go-mssqldb's behavior to that of CAST as shown in the following screenshot. Here, submittedString is the string form of the date that Go parsed to a time.Time and submitted to the submittedAsTime column. convertedInSQL is the result of a CAST statement that produces the correct DATETIME values directly from submittedString on the server.

image

To Reproduce

  1. Create the following MWE in an empty directory.
  2. Set the following environment variables to connect to your database server. The program will create a table called RoundingTest in the target database.
    • ROUNDINGTEST_HOST
    • ROUNDINGTEST_INITCATALOG
    • ROUNDINGTEST_USERNAME
    • ROUNDINGTEST_PASSWORD
  3. Run go run . in that directory.
  4. SELECT * from the RoundingTest table and note the differences between the columns.
main.go ```go package main import ( "context" "database/sql" "fmt" "net/url" "time" "github.com/microsoft/go-mssqldb" "github.com/sethvargo/go-envconfig" ) type Config struct { Host string `env:"HOST"` InitialCatalog string `env:"INITCATALOG"` Username string `env:"USERNAME"` Password string `env:"PASSWORD"` } func main() { var cn Config l := envconfig.PrefixLookuper("ROUNDINGTEST_", envconfig.OsLookuper()) if err := envconfig.ProcessWith(context.Background(), &envconfig.Config{ Target: &cn, Lookuper: l, }); err != nil { panic(err) } q := url.Values{} q.Add("database", cn.InitialCatalog) u := &url.URL{ Scheme: "sqlserver", Host: cn.Host, RawQuery: q.Encode(), } fmt.Printf("SQL connection URL before injecting credentials: %s\n", u.String()) u.User = url.UserPassword(cn.Username, cn.Password) pool, err := sql.Open("sqlserver", u.String()) if err != nil { panic(err) } stmt, err := pool.Prepare(`DROP TABLE RoundingTest`) if err != nil { panic(err) } _, err = stmt.Exec() // Ignore error since the table may not exist. stmt, err = pool.Prepare(`CREATE TABLE RoundingTest ( submittedString VARCHAR(50), submittedAsTime DATETIME, convertedInSQL DATETIME, )`) if err != nil { panic(err) } _, err = stmt.Exec() if err != nil { panic(err) } txn, err := pool.Begin() if err != nil { panic(err) } stmt, err = txn.Prepare(mssql.CopyIn("RoundingTest", mssql.BulkOptions{ KeepNulls: true, }, "submittedString", "submittedAsTime")) if err != nil { panic(err) } for i := 0; i < 10; i++ { isoString := fmt.Sprintf("2024-01-01T00:00:00.00%dZ", i) date, err := time.Parse("2006-1-2T15:4:5.999Z", isoString) if err != nil { panic(err) } _, err = stmt.Exec(isoString, date) if err != nil { panic(err) } } result, err := stmt.Exec() if err != nil { panic(err) } err = stmt.Close() if err != nil { panic(err) } err = txn.Commit() if err != nil { panic(err) } rowCount, _ := result.RowsAffected() fmt.Printf("Submitted %d rows.\n", rowCount) stmt, err = pool.Prepare(`UPDATE RoundingTest SET convertedInSQL = CAST(submittedString AS DATETIME)`) if err != nil { panic(err) } _, err = stmt.Exec() if err != nil { panic(err) } } ```
go.mod ```go module mmulvanny.local/go-mssqldb-roundingbug go 1.22 toolchain go1.22.1 require ( github.com/microsoft/go-mssqldb v1.7.0 github.com/sethvargo/go-envconfig v1.0.1 ) require ( github.com/golang-sql/civil v0.0.0-20220223132316-b832511892a9 // indirect github.com/golang-sql/sqlexp v0.1.0 // indirect golang.org/x/crypto v0.18.0 // indirect golang.org/x/text v0.14.0 // indirect ) ```
go.sum ```go github.com/Azure/azure-sdk-for-go/sdk/azcore v1.9.1 h1:lGlwhPtrX6EVml1hO0ivjkUxsSyl4dsiw9qcA1k/3IQ= github.com/Azure/azure-sdk-for-go/sdk/azcore v1.9.1/go.mod h1:RKUqNu35KJYcVG/fqTRqmuXJZYNhYkBrnC/hX7yGbTA= github.com/Azure/azure-sdk-for-go/sdk/azidentity v1.5.1 h1:sO0/P7g68FrryJzljemN+6GTssUXdANk6aJ7T1ZxnsQ= github.com/Azure/azure-sdk-for-go/sdk/azidentity v1.5.1/go.mod h1:h8hyGFDsU5HMivxiS2iYFZsgDbU9OnnJ163x5UGVKYo= github.com/Azure/azure-sdk-for-go/sdk/internal v1.5.1 h1:6oNBlSdi1QqM1PNW7FPA6xOGA5UNsXnkaYZz9vdPGhA= github.com/Azure/azure-sdk-for-go/sdk/internal v1.5.1/go.mod h1:s4kgfzA0covAXNicZHDMN58jExvcng2mC/DepXiF1EI= github.com/Azure/azure-sdk-for-go/sdk/security/keyvault/azkeys v1.0.1 h1:MyVTgWR8qd/Jw1Le0NZebGBUCLbtak3bJ3z1OlqZBpw= github.com/Azure/azure-sdk-for-go/sdk/security/keyvault/azkeys v1.0.1/go.mod h1:GpPjLhVR9dnUoJMyHWSPy71xY9/lcmpzIPZXmF0FCVY= github.com/Azure/azure-sdk-for-go/sdk/security/keyvault/internal v1.0.0 h1:D3occbWoio4EBLkbkevetNMAVX197GkzbUMtqjGWn80= github.com/Azure/azure-sdk-for-go/sdk/security/keyvault/internal v1.0.0/go.mod h1:bTSOgj05NGRuHHhQwAdPnYr9TOdNmKlZTgGLL6nyAdI= github.com/AzureAD/microsoft-authentication-library-for-go v1.2.1 h1:DzHpqpoJVaCgOUdVHxE8QB52S6NiVdDQvGlny1qvPqA= github.com/AzureAD/microsoft-authentication-library-for-go v1.2.1/go.mod h1:wP83P5OoQ5p6ip3ScPr0BAq0BvuPAvacpEuSzyouqAI= github.com/davecgh/go-spew v1.1.1 h1:vj9j/u1bqnvCEfJOwUhtlOARqs3+rkHYY13jYWTU97c= github.com/davecgh/go-spew v1.1.1/go.mod h1:J7Y8YcW2NihsgmVo/mv3lAwl/skON4iLHjSsI+c5H38= github.com/golang-jwt/jwt/v5 v5.2.0 h1:d/ix8ftRUorsN+5eMIlF4T6J8CAt9rch3My2winC1Jw= github.com/golang-jwt/jwt/v5 v5.2.0/go.mod h1:pqrtFR0X4osieyHYxtmOUWsAWrfe1Q5UVIyoH402zdk= github.com/golang-sql/civil v0.0.0-20220223132316-b832511892a9 h1:au07oEsX2xN0ktxqI+Sida1w446QrXBRJ0nee3SNZlA= github.com/golang-sql/civil v0.0.0-20220223132316-b832511892a9/go.mod h1:8vg3r2VgvsThLBIFL93Qb5yWzgyZWhEmBwUJWevAkK0= github.com/golang-sql/sqlexp v0.1.0 h1:ZCD6MBpcuOVfGVqsEmY5/4FtYiKz6tSyUv9LPEDei6A= github.com/golang-sql/sqlexp v0.1.0/go.mod h1:J4ad9Vo8ZCWQ2GMrC4UCQy1JpCbwU9m3EOqtpKwwwHI= github.com/google/go-cmp v0.6.0 h1:ofyhxvXcZhMsU5ulbFiLKl/XBFqE1GSq7atu8tAmTRI= github.com/google/go-cmp v0.6.0/go.mod h1:17dUlkBOakJ0+DkrSSNjCkIjxS6bF9zb3elmeNGIjoY= github.com/google/uuid v1.6.0 h1:NIvaJDMOsjHA8n1jAhLSgzrAzy1Hgr+hNrb57e+94F0= github.com/google/uuid v1.6.0/go.mod h1:TIyPZe4MgqvfeYDBFedMoGGpEw/LqOeaOT+nhxU+yHo= github.com/kylelemons/godebug v1.1.0 h1:RPNrshWIDI6G2gRW9EHilWtl7Z6Sb1BR0xunSBf0SNc= github.com/kylelemons/godebug v1.1.0/go.mod h1:9/0rRGxNHcop5bhtWyNeEfOS8JIWk580+fNqagV/RAw= github.com/microsoft/go-mssqldb v1.7.0 h1:sgMPW0HA6Ihd37Yx0MzHyKD726C2kY/8KJsQtXHNaAs= github.com/microsoft/go-mssqldb v1.7.0/go.mod h1:kOvZKUdrhhFQmxLZqbwUV0rHkNkZpthMITIb2Ko1IoA= github.com/pkg/browser v0.0.0-20240102092130-5ac0b6a4141c h1:+mdjkGKdHQG3305AYmdv1U2eRNDiU2ErMBj1gwrq8eQ= github.com/pkg/browser v0.0.0-20240102092130-5ac0b6a4141c/go.mod h1:7rwL4CYBLnjLxUqIJNnCWiEdr3bn6IUYi15bNlnbCCU= github.com/pmezard/go-difflib v1.0.0 h1:4DBwDE0NGyQoBHbLQYPwSUPoCMWR5BEzIk/f1lZbAQM= github.com/pmezard/go-difflib v1.0.0/go.mod h1:iKH77koFhYxTK1pcRnkKkqfTogsbg7gZNVY4sRDYZ/4= github.com/sethvargo/go-envconfig v1.0.1 h1:9wglip/5fUfaH0lQecLM8AyOClMw0gT0A9K2c2wozao= github.com/sethvargo/go-envconfig v1.0.1/go.mod h1:OKZ02xFaD3MvWBBmEW45fQr08sJEsonGrrOdicvQmQA= github.com/stretchr/testify v1.8.4 h1:CcVxjf3Q8PM0mHUKJCdn+eZZtm5yQwehR5yeSVQQcUk= github.com/stretchr/testify v1.8.4/go.mod h1:sz/lmYIOXD/1dqDmKjjqLyZ2RngseejIcXlSw2iwfAo= golang.org/x/crypto v0.18.0 h1:PGVlW0xEltQnzFZ55hkuX5+KLyrMYhHld1YHO4AKcdc= golang.org/x/crypto v0.18.0/go.mod h1:R0j02AL6hcrfOiy9T4ZYp/rcWeMxM3L6QYxlOuEG1mg= golang.org/x/net v0.20.0 h1:aCL9BSgETF1k+blQaYUBx9hJ9LOGP3gAVemcZlf1Kpo= golang.org/x/net v0.20.0/go.mod h1:z8BVo6PvndSri0LbOE3hAn0apkU+1YvI6E70E9jsnvY= golang.org/x/sys v0.16.0 h1:xWw16ngr6ZMtmxDyKyIgsE93KNKz5HKmMa3b8ALHidU= golang.org/x/sys v0.16.0/go.mod h1:/VUhepiaJMQUp4+oa/7Zr1D23ma6VTLIYjOOTFZPUcA= golang.org/x/text v0.14.0 h1:ScX5w1eTa3QqT8oi6+ziP7dTV1S2+ALU0bI+0zXKWiQ= golang.org/x/text v0.14.0/go.mod h1:18ZOQIKpY8NJVqYksKHtTdi31H5itFRjB5/qKTNYzSU= gopkg.in/yaml.v3 v3.0.1 h1:fxVm/GzAzEWqLHuvctI91KS9hhNmmWOoWu0XTYJS7CA= gopkg.in/yaml.v3 v3.0.1/go.mod h1:K4uyk7z7BCEPqu6E+C64Yfv1cQ7kz7rIZviUmN+EgEM= ```

Expected behavior go-mssqldb should round time.Time values to DATETIMEs in the same way that SQL Server does.

Further technical details

SQL Server version: SQL Server 2019. Operating system: Server is a docker container; client is Ubuntu 20.04.1. Table schema: see the CREATE TABLE statement in main.go, above.

shueybubbles commented 3 months ago

It seems the logic to "round to the nearest 1/300 of a second" isn't quite right.