Datetime field overflow with sql server 2008

[GoogleCodeExporter]

SQLExecute: {22008} [Microsoft][SQL Server Native Client 11.0]Datetime 
字段溢出。秒的小数精度超出了在参数绑定中指定的小数位��
�。

PATCH:
--- a/param.go  Fri Nov 15 09:34:59 2013 +1100
+++ b/param.go  Tue Dec 10 15:34:00 2013 +0800
@@ -102,7 +102,7 @@
                        Hour:     api.SQLUSMALLINT(d.Hour()),
                        Minute:   api.SQLUSMALLINT(d.Minute()),
                        Second:   api.SQLUSMALLINT(d.Second()),
-                       Fraction: api.SQLUINTEGER(d.Nanosecond()),
+                       Fraction: 0,
                }
                p.Data = &b
                buf = unsafe.Pointer(&b)
@@ -113,7 +113,9 @@
                if decimal <= 0 {
                        // represented as yyyy-mm-dd hh:mm:ss.fff format in ms sql server
                        decimal = 3
+                       b.Fraction = api.SQLUINTEGER(d.Nanosecond() / 
int(time.Millisecond))
                }
                size = 20 + api.SQLULEN(decimal)
        case []byte:

Original issue reported on code.google.com by runner....@gmail.com on 10 Dec 2013 at 7:38

[GoogleCodeExporter]

Please, provide a small example program to demonstrate the problem. Thank you.

Alex

Original comment by alex.bra...@gmail.com on 11 Dec 2013 at 12:41

[GoogleCodeExporter]

Original comment by alex.bra...@gmail.com on 11 Dec 2013 at 12:46

• Changed state: WaitingForReply

[GoogleCodeExporter]

//  go test "-msdriver=sql server native client 11.0" -mssrv=192.168.1.2 
-msdb=tpt_data_386_test -msuser=tpt -m spass=extreme -v -run=MS
func TestMSSQLDatetimeParam(t *testing.T) {
    db, sc, err := mssqlConnect()
    if err != nil {
        t.Fatal(err)
    }
    defer closeDB(t, db, sc, sc)

    if !is2008OrLater(db) {
        t.Skip("skipping test; needs MS SQL Server 2008 or later")
    }

    db.Exec("drop table dbo.temp")
    exec(t, db, "create table dbo.temp (dt datetime)")

    expect := time.Date(2007, 5, 8, 12, 35, 29, 1234567e2, time.Local)
    _, err = db.Exec("insert into dbo.temp (dt) values (?)", expect)
    if err != nil {
        t.Fatal(err)
    }
    var got time.Time
    err = db.QueryRow("select top 1 dt from dbo.temp").Scan(&got)
    if err != nil {
        t.Fatal(err)
    }
    if expect != got {
        t.Fatalf("expect %v, but got %v", expect, got)
    }

    exec(t, db, "drop table dbo.temp")
}

Original comment by runner....@gmail.com on 7 Jan 2014 at 3:31

[GoogleCodeExporter]

Your datatime overflows.

MS SQL Server only supports 3 digits after decimal point in seconds. See for 
example http://msdn.microsoft.com/en-us/library/ms187819.aspx.

If you need precision better then that, use new datetime2 type. If you don't 
need the precision, just round your time.Time values before posting them into 
your database server.

Alex

Original comment by alex.bra...@gmail.com on 7 Jan 2014 at 4:18

• Changed state: WontFix

[GoogleCodeExporter]

I think you should let it automatically adjust precision in driver.

Original comment by runner....@gmail.com on 7 Jan 2014 at 5:29

[GoogleCodeExporter]

I think differently. Sorry.

Alex

Original comment by alex.bra...@gmail.com on 7 Jan 2014 at 5:43

[GoogleCodeExporter]

Issue 45 has been merged into this issue.

Original comment by alex.bra...@gmail.com on 25 Jun 2014 at 4:12