Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-07-17 : 08:24:13
|
| Chandra writes "I created a simple test case to illustrate the error. Create a table: create table ttest (x datetime)Create a procedure:create procedure ptest (@p1 datetime) as insert into ttest values (@p1)Create a simple C++ app and try to call the procedure ptest. The code snippet for executing the procedure is shown below: TIMESTAMP_STRUCT date_time; date_time.day = 14; date_time.month = 7; date_time.year = 2; date_time.hour = 16; date_time.minute = 54; date_time.second = 50; date_time.fraction = 0; SQLINTEGER bufferLength = sizeof (date_time); SQLPOINTER valuePtr = (SQLPOINTER)&date_time; SQLINTEGER lenptr = sizeof (date_time); TCHAR *stmt = L"{CALL ptest(?)}"; rc = SQLPrepare(hStmt, stmt, SQL_NTS); rc = SQLBindParameter(hStmt, 1, SQL_PARAM_INPUT, SQL_C_TYPE_TIMESTAMP, SQL_TYPE_TIMESTAMP, 23, 3, (SQLPOINTER)&date_time, sizeof (date_time), &lenptr); if (rc != SQL_SUCCESS) sqlerror(hStmt); rc = SQLExecute(hStmt);SQLExecute returns the following error:"[Microsoft][ODBC SQL Server Driver]Datetime field overflow"I tried to look at the docs on MSDN and other places to no avail. Any suggestions/hints are welcome.Thanks in advance." |
|
|
M.E.
Aged Yak Warrior
539 Posts |
Posted - 2002-07-17 : 11:55:03
|
Unfortunately c+ is really really foriegn to me...Could youy post up what hStmt is when you execute it quote:
rc = SQLExecute(hStmt);
-----------------------Take my advice, I dare ya |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-07-17 : 11:58:38
|
| Actually, the reason is the value you have for year. SQL Server's minimum date value is January 1, 1753 00:00:00; you indicated July 14, 2. Adjusting the year to a legal value should do the trick. |
|
|
|
rmao
Starting Member
7 Posts |
Posted - 2002-08-23 : 18:20:03
|
| Actually, I've had this problem when I'm positive that the datetime I've entered was valid. I tried the same code on other ODBC drivers and had no errors. Only SQL Server's ODBC driver doesn't seem to like binding datetime parameters for any sort of SQL statement. (I've had the same problem with inserts, queries and updates). Interestingly enough, the problem goes away when binding arrays of parameters. i.e. If I was going to do a batch insert with the statement."INSERT INTO Table1(Col1, Col2) VALUES (?, ?);"And I set the the paramset size > 1.sqlReturn = SQLSetStmtAttr(db->hStmt, SQL_ATTR_PARAMSET_SIZE, (SQLPOINTER)nCount, 0);and I bind my parameters...sqlReturn = SQLBindParameter(db->hStmt, (SQLUSMALLINT), 1, PARAM_INPUT, nCType1, nColumnType1, nColumnSize, 0, DataArray1, (SQLINTEGER)iSize, pIndPtr);sqlReturn = SQLBindParameter(db->hStmt, (SQLUSMALLINT), 2, PARAM_INPUT, nCType2, nColumnType2, nColumnSize, 0, DataArray2, (SQLINTEGER)iSize, pIndPtr);I get don't get the datetime field overflow error.The error only occurs when the paramset size is 1.Any ideas anyone? I've had to resort to formatting the datetime into a string manually, which also works without error, so I'm prettysure that the datetime isn't actually invalid.(i.e. INSERT INTO Table1(col1, col2) VALUES (?, '08/15/2002 9:18:10')) |
|
|
|
|
|
|
|
|
Error datetime overflow when calling a procedure using ODBC CALL API