Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    55

    Unanswered: 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.

    Quote Originally Posted by Error Message
    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...

    Code:
    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...

    Code:
    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.
    Cheers

  2. #2
    Join Date
    Aug 2005
    Posts
    55
    hey everyone, when i try to run the the following trace result as a query, i get an error near 2007...

    Code:
    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'
    are there meant to be the double single quotes('') before and after the date?

    Cheers,

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by freefall
    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

  4. #4
    Join Date
    Nov 2006
    Location
    Quebec
    Posts
    172
    Quote Originally Posted by Pat Phelan
    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'''
    Less is more.
    How long is now?
    http://www.lesouterrain.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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:
    Code:
    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.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •