By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,574 Members | 1,977 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,574 IT Pros & Developers. It's quick & easy.

SQL STATE 22003: Numeric value out of range with TIME data type

P: n/a
Hi,

I am trying to insert a TIME value in a SQL Server 2008 database using
a simple ODBC C program. I follow the steps below:

1. Connect and Allocate Statement handle
2. Prepare a Statement as:
rc = SQLPrepare(hstmt, "insert into table1 values (?)" ,
SQL_NTS);
(NB: Table1 has only one column of type TIME in SQL Server 2008)
3. Bind the parameter:
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,
SQL_C_BINARY,SQL_SS_TIME2, 16,7, &buffer,
sizeof(SQL_SS_TIME2_STRUCT ), 0);

(where, buffer is of type SQL_SS_TIME2_STRUCT and buffer.hour = 12;
buffer.minute=12; buffer.second =12; buffer.fraction = 0)

I also tried SQL_C_DEFAULT instead of SQL_C_BINARY and got the same
error.
4. rc = SQLExecute(htsmt)

Appreciate any help to resolve this.

Thanks in Advance,
Navneet
Nov 5 '08 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Navs (na***********@gmail.com) writes:
I am trying to insert a TIME value in a SQL Server 2008 database using
a simple ODBC C program. I follow the steps below:

1. Connect and Allocate Statement handle
2. Prepare a Statement as:
rc = SQLPrepare(hstmt, "insert into table1 values (?)" ,
SQL_NTS);
(NB: Table1 has only one column of type TIME in SQL Server 2008)
3. Bind the parameter:
rc = SQLBindParameter(hstmt,1,SQL_PARAM_INPUT,
SQL_C_BINARY,SQL_SS_TIME2, 16,7, &buffer,
sizeof(SQL_SS_TIME2_STRUCT ), 0);

(where, buffer is of type SQL_SS_TIME2_STRUCT and buffer.hour = 12;
buffer.minute=12; buffer.second =12; buffer.fraction = 0)

I also tried SQL_C_DEFAULT instead of SQL_C_BINARY and got the same
error.
4. rc = SQLExecute(htsmt)
ODBC programming is nothing I normally do, but I've been reading the
manual, and it seems to me that the problem is the last parameter. I
this this needs to be address to some place where you have written
sizeof(SQL_SS_TIME2_STRUCT). In the documentatation for
SQLBindParameter, I found:

Note Application developers are strongly discouraged from specifying
a null pointer for StrLen_or_IndPtr when the data type of the parameter
is SQL_C_BINARY. To ensure that a driver does not unexpectedly truncate
SQL_C_BINARY data, StrLen_or_IndPtr should contain a pointer to a valid
length value.

I also found this note in SQL Server Books Online 2008 on:
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_2devguide/html/7ac098db-9147-4883-8da9-a58ab24a0d31.htm

If the byte length of the data does not equal the size of the struct
required by the SQL type, a diagnostic record is generated with
SQLSTATE 22003 and the message "Numeric value out of range".


--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

Nov 5 '08 #2

This discussion thread is closed

Replies have been disabled for this discussion.