Problems inserting datetime object into sql server 2005

hey everyone,

Im trying run a stored procedure, but I am getting the following message when trying to execute it.

Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

which seems strange, as there is no integer going to a datetime field. Also, it does not return the value that is incorrect, nor the parameter that is failing. If there is anyway to view this in VS 2005 Team suite, please let me know.

It should also be noted that when I execute this sproc from within sql management studio, it executes fine.

This is the stored procedure im trying to execute...

CREATE PROCEDURE [dbo].[sp_UpdateRequest]

@.request_id INT,
@.request_client_id INT,
@.request_description VARCHAR(150), @.request_date DATETIME,
@.request_edit_date DATETIME, @.request_status VARCHAR(25), @.request_approve_date DATETIME,
@.request_activity_code VARCHAR(7), @.request_department_code VARCHAR(3), @.request_participants INT,
@.request_activity_date DATETIME, @.request_activity_due_date DATETIME,
@.request_company_id INT, @.request_company_code INT,
@.request_notes VARCHAR(2000)

AS
BEGIN

UPDATE invoice_requests

SET request_client_id = @.request_client_id, request_description = @.request_description,
request_date = @.request_date, request_edit_date = @.request_edit_date,
request_status = @.request_status, request_approve_date = @.request_approve_date,
request_activity_code = @.request_activity_code,
request_department_code = @.request_department_code,
request_participants = @.request_participants,
request_activity_date = @.request_activity_date,
request_activity_due_date = @.request_activity_due_date,
request_company_code = @.request_company_code, request_company_id = @.request_company_id,
request_notes = @.request_notes

WHERE request_id = @.request_id

END
GO

in my .net app, all the datetime objects are valid dates, so I could not see why this would be generating the error that it is, so I ran the SQL Profiler, and this was the output. Again, i cannot see where im going wrong...

please note, I have seperated the output from the single slab of text, making sure I didnt remove anything...

exec sp_executesql
N'EXECUTE sp_UpdateRequest
@.request_id,
@.request_client_id,
@.request_description,
@.request_date,
@.request_edit_date,
@.request_status,
@.request_approve_date,
@.request_activity_code,
@.request_participants,
@.request_activity_date,
@.request_activity_due_date,
@.request_company_id,
@.request_company_code,
@.request_notes',

N'@.request_id int,
@.request_client_id int,
@.request_description varchar(8000),
@.request_date datetime,
@.request_edit_date datetime,
@.request_status varchar(5),
@.request_approve_date datetime,
@.request_activity_code varchar(8000),
@.request_department_code varchar(8000),
@.request_participants int,
@.request_activity_date datetime,
@.request_activity_due_date datetime,
@.request_company_id int,
@.request_company_code int,
@.request_notes varchar(8000)',

@.request_id=5,
@.request_client_id=1,
@.request_description='',
@.request_date=''2007-11-22 16:34:32:997'',
@.request_edit_date=''2007-11-22 16:34:32:997'',
@.request_status='Draft',
@.request_approve_date=''1970-01-01 00:00:00:000'',
@.request_activity_code='',
@.request_department_code='',
@.request_participants=0,
@.request_activity_date=''2007-11-29 00:00:00:000'',
@.request_activity_due_date=''2007-11-23 00:00:00:000'',
@.request_company_id=0,
@.request_company_code=1,
@.request_notes=''

any help on this would be greatly appreciated.
Cheershey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

exec sp_executesql N'EXECUTE sp_UpdateRequest @.request_id, @.request_client_id, @.request_description, @.request_date, @.request_edit_date, @.request_status, @.request_approve_date, @.request_activity_code, @.request_participants,
@.request_activity_date, @.request_activity_due_date, @.request_company_id, @.request_company_code, @.request_notes',N'@.request_id int,@.request_client_id int,@.request_description varchar(6),@.request_date datetime,@.request_edit_date
datetime,@.request_status varchar(5),@.request_approve_date datetime,@.request_activity_code varchar(4),@.request_department_code varchar(3),@.request_participants int,@.request_activity_date datetime,@.request_activity_due_date
datetime,@.request_company_id int,@.request_company_code int,@.request_notes varchar(11)',@.request_id=1,@.request_client_id=1,@.r equest_description='Justin',@.request_date=''2007-11-23 08:59:04:250'',@.request_edit_date=''2007-11-23
08:59:04:250'',@.request_status='Draft',@.request_ap prove_date=''1970-01-01 00:00:00:000'',@.request_activity_code='0000',@.requ est_department_code='022',@.request_participants=50 ,@.request_activity_date=''2007-11-23
00:00:00:000'',@.request_activity_due_date=''2007-11-30 00:00:00:000'',@.request_company_id=5292,@.request_c ompany_code=1,@.request_notes='Other Notes'

are there meant to be the double single quotes('') before and after the date?

Cheers,|||are there meant to be the double single quotes('') before and after the date?No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP|||No, there should be a single apostrophe surrounding (before and after) date constants.

Your constant ''2007-11-23 08:59:04:250'' syntactically ought to be '2007-11-23 08:59:04:250' for use in Microsoft SQL Server.

-PatP

I'm not sure because it's already surrounded by single quotes.

maybe you can try '''2007-11-23 08:59:04:250'''|||Sorry, I should have looked at your whole example.

There are multiple ways that I could parse what you've posted. The one that makes the most sense to me at the moment is:exec sp_executesql N'EXECUTE sp_UpdateRequest
@.request_id, @.request_client_id, @.request_description
, @.request_date, @.request_edit_date, @.request_status
, @.request_approve_date, @.request_activity_code, @.request_participants
, @.request_activity_date, @.request_activity_due_date, @.request_company_id
, @.request_company_code, @.request_notes'', N''@.request_id int
, @.request_client_id int, @.request_description varchar(6), @.request_date datetime
, @.request_edit_date datetime, @.request_status varchar(5), @.request_approve_date datetime
, @.request_activity_code varchar(4), @.request_department_code varchar(3), @.request_participants int
, @.request_activity_date datetime, @.request_activity_due_date datetime, @.request_company_id int
, @.request_company_code int, @.request_notes varchar(11)''
, @.request_id = 1, @.request_client_id = 1, @.request_description = ''Justin''
, @.request_date = ''2007-11-23 08:59:04:250'', @.request_edit_date = ''2007-11-23 08:59:04:250''
, @.request_status = ''Draft'', @.request_approve_date = ''1970-01-01 00:00:00:000''
, @.request_activity_code = ''0000'', @.request_department_code = ''022'', @.request_participants = 50
, @.request_activity_date = ''2007-11-23 00:00:00:000'', @.request_activity_due_date = ''2007-11-30 00:00:00:000''
, @.request_company_id = 5292, @.request_company_code = 1, @.request_notes = ''Other Notes''
'See if that represents what you think you meant, and let me know how it works for you.

-PatPsql

Problems converting varchar to smallmoney

Hi

My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.

The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)

As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.

What do i have to do to convert the value as it is entered by the user?

Thanks in advance

t.

Statement:

INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()

DDL for concerned database:

CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

I am little confused. In your INSERT INTO statement, you are converting {3} and {4} to numeric, while the datatype for these two columns are smallmoney. Why don't you use smallmoney directly in the INSERT statement?

The reason it rounds up for you is that the default scale (max number of decimal digits) of numeric data type is 0. It would work fine if you use numeric(20, 2) for example. See "decimal and numeric (Transact-SQL)" in SQL Server Book Online for details.

|||

thx

seems to work now using smallmoney directly or numeric (20,2)