SQL Server 2010, smalldatetime insert

jkirkerx

I'll swear I have this right The field is smalldatetime. I'm just picking up the record and inserting it. On select, I used

CONVERT(varchar(33), ADD_DATE, 121) AS ADD_DATE,

Oninsert, the sql looks like this

'1900-01-01 00:00:00.000'

I read up on sql date times and the different formats. If I pick it raw, and try o insert it, I get an object error. In SQL Manager, I get

Conversion failed when converting character string to smalldatetime data type.

Warning: sqlsrv_fetch_array() expects parameter 1 to be resource, bool given in C:\App\Dev\PCAD\repository\projectOrder.repository.php on line 1182
Array ( [0] => Array ( [0] => 22007 [SQLSTATE] => 22007 [1] => 295 [code] => 295 [2] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting character string to smalldatetime data type. [message] => [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Conversion failed when converting character string to smalldatetime data type. ) ) addProjectCost 1749 INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 ) - 1

If it ain't broke don't fix it Discover my world at jkirkerx.com

Mycroft Holmes · '1900-01-01 00:00:00.000'

That is why you used parameterized queries instead of strings.

Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP

Victor Nijegorodov · '1900-01-01 00:00:00.000'

jkirkerx wrote:

INSERT INTO [proj_cost] ( Proj_ID, Proj_Stage, Vers_ID, Task_ID, Task_Oper, Task_Class, Vend_ID, CO_Proj_Type, CO_Type, CO_Type_D, CO_Value, Cost, ADD_NO, ADD_DATE, ADD_CODE, Pref_Vendor, User_Last_Upd, Delete_Flag ) VALUES ( '2549', 'engineering', '0', 'TK_AT_001', '85', 'OP', '', '39', 'PoolSpa', 'REF_AT_002', '', '25', '175', '0', '1900-01-01 00:00:00.000', '', 'jimk', 0 )

I guess the VALUES list does not correspond the fields list, so the value form ADD_DATE seems to be '0' rather than '1900-01-01 00:00:00.000'.

jkirkerx · '1900-01-01 00:00:00.000'

I thought about what you both said, and would like to say thanks and show gratitude as well. I went back and keep the Select statement raw, took the convert out. The dumped my CRUD function to add the record, and converted the calling function to SQL. Then used PHP to convert the raw date object I picked up to a string, and declared it in SQL. I suppose I could go back to my add CRUD function and do the conversion there instead

$result2 = sqlsrv_query($conn, $query2) or die(" setDbProjectCost " . __LINE__ . " - " . $query2 . " - " . print_r(sqlsrv_errors()));
if (sqlsrv_has_rows($result2)) {
$row2 = sqlsrv_fetch_array($result2);
$addDateString = $row2[13]->format('Y-m-d H:i:s') . '.000';

$query3 = "
DECLARE @addDate AS VARCHAR(33) = '$addDateString';
INSERT INTO \[proj\_cost\] 
VALUES ... @addDate

If it ain't broke don't fix it Discover my world at jkirkerx.com