MSSQL datetime insert through code.google.com/p/odbc

[galf...@gmail.com]

Hi guys.

Have a problem and can't find any solution for 2 days already.

I have MSSQL DB with table1 datetime1 column of datetime type.

I have to INSERT into table1.datetime1 column value of time.Time format.

I have tried many different ways to do it but still have an error 

Error: SQLExecute: {22018} [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification

Piese of code:

    res, err := DB.Exec(`INSERT INTO t_transaction 

            (id, cat_to_id, amt, date_added, comment, user_id, cat_from_id, planned, date_accurate)

            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,

        "d049cfcd-b4b8-48ef-6b4b-a27768cd5583"/*t.Id.String()*/, 

        "7bdac2c0-bb10-4d29-493d-1d7c84c7c753"/*t.CatToId.String()*/, 

        10/*t.Amt*/,

        "20140319 13:30:20"/*t.DateAdded.Format("2014-03-19 11:30")*/, 

        "cmt"/*t.Comment*/, 

        "6ba7b810-9dad-11d1-80b4-00c04fd430c8"/*t.UserId.String()*/, 

        "e91ac629-fbee-47c3-5ff6-0a159deb46a0"/*t.CatFromId.String()*/, 

        0/*t.Planned*/, 

        "20140319 13:30:20"/*t.DateAccurate*/)

I commented real code and provided fixed values for test purposes.

thank you for any help.

[Konstantin Khomoutov]

On Wed, 19 Mar 2014 04:02:05 -0700 (PDT)
galf...@gmail.com wrote:

> Hi guys.
> Have a problem and can't find any solution for 2 days already.
> I have MSSQL DB with table1 datetime1 column of datetime type.
> I have to INSERT into table1.datetime1 column value of time.Time
> format.
>
> I have tried many different ways to do it but still have an error

> *Error: SQLExecute: {22018} [Microsoft][SQL Server Native Client
> 11.0]Invalid character value for cast specification*
>
> *Piese of code:*

>
> res, err := DB.Exec(`INSERT INTO t_transaction
> (id, cat_to_id, amt, date_added, comment, user_id,
> cat_from_id, planned, date_accurate)
> VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
> "d049cfcd-b4b8-48ef-6b4b-a27768cd5583"/*t.Id.String()*/,
> "7bdac2c0-bb10-4d29-493d-1d7c84c7c753"/*t.CatToId.String()*/,
> 10/*t.Amt*/,
> "20140319 13:30:20"/*t.DateAdded.Format("2014-03-19 11:30")

Uh, what type is t.DateAdded in the real code? time.Time?
Why you don't pass it as is then?
The whole idea of using prepared statements or parameterized queries
(essentially the same thing anyway) is to leave all the conversion
stuff to the database driver. Why not supply a time.Time value to it?

[...]

[brainman]

On Wednesday, 19 March 2014 22:02:05 UTC+11, galf...@gmail.com wrote:

> ... have an error 

> Error: SQLExecute: {22018} [Microsoft][SQL Server Native Client 11.0]Invalid character value for cast specification

I suspect, you are sending this string "20140319 13:30:20" that your mssql server needs to convert to datetime. It cannot do that, because it does not understand your format. I tried using "2014-03-19 13:30:20" and that works for me. But I wouldn't use string parameter for datetime fields, I would just pass time.Time as is to the database driver instead.

Here is my code:

diff --git a/mssql_test.go b/mssql_test.go

--- a/mssql_test.go

+++ b/mssql_test.go

@@ -1218,3 +1218,28 @@

  t.Fatal("comparison fails")

  }

 }

+

+func TestALEX(t *testing.T) {

+ db, sc, err := mssqlConnect()

+ if err != nil {

+ t.Fatal(err)

+ }

+ defer closeDB(t, db, sc, sc)

+

+ db.Exec("drop table dbo.temp")

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

+ // dt := "20140319 13:30:20" // rejected by mssql server, invalid date format

+ dt := "2014-03-19 13:30:20" // works, use correctly formatted string

+ // dt := time.Now().Round(time.Second) // works, just use time.Time

+ _, err = db.Exec(`insert into dbo.temp (dt) values (?)`, dt)

+ if err != nil {

+ t.Fatal(err)

+ }

+ var got time.Time

+ err = db.QueryRow("select dt from dbo.temp").Scan(&got)

+ if err != nil {

+ t.Fatal(err)

+ }

+ t.Logf("got=%v", got)

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

+}

Alex

[Maksym Puzin]

"The whole idea of using prepared statements or parameterized queries
(essentially the same thing anyway) is to leave all the conversion
stuff to the database driver. "

That sounds just great but if I use it as is it ends up with: SQLExecute: {22008} [Microsoft][SQL Server Native Client 11.0]Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding.

[Maksym Puzin]

I don't wnat use string type in DB for keeping date values - i use datetime. But passing time.Time as is it ends up with an error (
Solution for now is to convrt time.Time to string value in "2006-01-02 15:04:05" and then pass it to datetime column in MSSQL. But it is not cool as for Go! i guess, must be some better way to deal with time.Time -> MSSQL.datetime.

thank you.

[Konstantin Khomoutov]

On Thu, 20 Mar 2014 01:58:08 -0700 (PDT)
Maksym Puzin <mge...@gmail.com> wrote:

> "The whole idea of using prepared statements or parameterized queries
> (essentially the same thing anyway) is to leave all the conversion
> stuff to the database driver. "
> That sounds just great but if I use it as is it ends up with:
>
>

> *SQLExecute: {22008} [Microsoft][SQL Server Native Client 11.0]

> Datetime field overflow. Fractional second precision exceeds the

> scale specified in the parameter binding.*

OK, so now we finally know the root cause of your problem.
Please consider filing a bug against code.google.com/p/odbc [1]
(specify Go version, platform, SQL Server version, and the data type
of the column for which insertion fails; sqlncli.dll version is known
from the error cited above so include it in the bug report, too).

Your problem looks similar to [2] but since you provided too few
information on the problem it's hard to guess.

1. https://code.google.com/p/odbc/issues/list
2. https://code.google.com/p/odbc/issues/detail?id=14

[brainman]

On Thursday, 20 March 2014 20:01:29 UTC+11, Maksym Puzin wrote:
...

You don’t need to convert time.Time into string to insert it into datetime MSSQL field. If you uncomment the line

// dt := time.Now().Round(time.Second) // works, just use time.Time

in my code, you will see that it works just fine. The “Datetime field overflow. …” error you’re seeing while sending time.Time to the server is because your time.Time value gets truncated. MSSQL server will allow no more than 3 decimal digits for seconds. Just round your values properly.

Alex

[Maksym Puzin]

Konstantin, it is so common operation - to INSERT time.Time to MSSQL.datetime field. Are you sure it could be a bug there?
Ok, i will create an issue with details.

[Maksym Puzin]

brainman, yes, you right! rounding time.Time to seconds is good and working approach. thank you!
But still i don't think it should be on developer's side to deal. It's driver's stuff i guess...

[Konstantin Khomoutov]

On Thu, 20 Mar 2014 03:07:39 -0700 (PDT)
Maksym Puzin <mge...@gmail.com> wrote:

[...]

> > > *SQLExecute: {22008} [Microsoft][SQL Server Native Client 11.0]
> > > Datetime field overflow. Fractional second precision exceeds the
> > > scale specified in the parameter binding.*
> >
> > OK, so now we finally know the root cause of your problem.
> > Please consider filing a bug against code.google.com/p/odbc [1]
> > (specify Go version, platform, SQL Server version, and the data
> > type of the column for which insertion fails; sqlncli.dll version
> > is known from the error cited above so include it in the bug
> > report, too).

[...]

> Konstantin, it is so common operation - to INSERT time.Time to
> MSSQL.datetime field. Are you sure it could be a bug there?

I don't quite follow your reasoning. You rightfully claim that if you
have a column of type datetime on the server and try to insert a value
of type time.Time into it, it should "just work" -- the driver is
supposed to provide the necessary conversion. So if you're observing
there's not the case, two possibilities are possible:

* It's a bug in the driver (or in ODBC layer or in backend ODBC driver,
provided by the SQL Server suite, which is less likely but possible).
* It's an intended behaviour. This *might* as well be true as I gather
from reading the other part of this thread: if the datetime SQL
Server's type has lower precision than time.Time, just automatically
rounding them down is not the right thing for the driver to do.
If yes, this behaviour should be documented at least.

> Ok, i will create an issue with details.

I'd say yes, that would at least increase the visibility of the problem
for the next guy to hit it.

[Konstantin Khomoutov]

On Thu, 20 Mar 2014 03:13:07 -0700 (PDT)
Maksym Puzin <mge...@gmail.com> wrote:

> > You don’t need to convert time.Time into string to insert it into
> > datetime MSSQL field. If you uncomment the line
> >
> > // dt := time.Now().Round(time.Second) // works, just use time.Time
> >
> > in my code, you will see that it works just fine. The “Datetime
> > field overflow. …” error you’re seeing while sending time.Time to
> > the server is because your time.Time value gets truncated. MSSQL
> > server will allow no more than 3 decimal digits for seconds. Just
> > round your values properly.
> >

> brainman, yes, you right! rounding time.Time to seconds is good and
> working approach. thank you!
> But still i don't think it should be on developer's side to deal.
> It's driver's stuff i guess...

I disagree. Silently rounding your values would provide unexpected
behaviour since what you SELECT back will not be what you INSERTed.

[Govert Versluis]

Although not strictly helping you with your immediate problem, it might help to be aware of the difference between the datetime [1] and datetime2 [2] column types.

Datetime2 has a higher precision and broader range, and is recommended for new work, whereas datetime is kept as a legacy type.

Alex: This might help you too?

[1] http://msdn.microsoft.com/en-us/library/ms187819.aspx

[2] http://msdn.microsoft.com/en-us/library/bb677335.aspx

[brainman]

On Thursday, 20 March 2014 21:13:07 UTC+11, Maksym Puzin wrote:

> ... But still i don't think it should be on developer's side to deal. It's driver's stuff i guess...

I disagree. You are the only one who knows how to round. Would you be happy if driver leaves out hours, minutes and seconds? What about minutes and seconds? You're the only one who can make that call. Just round it as you see fit.

Alex

[brainman]

On Friday, 21 March 2014 01:43:28 UTC+11, Govert Versluis wrote:

> ... Alex: This might help you too?

Oh, I do know about datetime and datetime2. code.google.com/p/odbc driver supports both. Thank you.

Alex