Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    35

    Unanswered: Insert only time?

    When I try to insert a record with only the time (e.g. '19:00') as parameter
    for a datetime field in a stored procedure, I get the following error:

    SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.

    At application level, the field is imported into an object like this:

    Code:
        myEvent.EventTime = CDate(txtEventTime.Text)
    It works fine for the EventDate part, which passes only the date (e.g. '9.8.2004') to the same stored procedure.
    What could I be doing wrong?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    U$100.00 the Date "part" is null

    And you are keeping them as 2 separate fields....and by putting together it causes the whole thing (well in sql anyway) to by null

    if you just supplied a time as 1 field, it would use 01/01/1900 as the default date...

    Make sure datetime is 1 field before you pass it sql

    OH...just a guess...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    35
    When the parameter gets passed to the stored procedure, it contains the
    following data (for example):

    #7:00:00 PM#

    so it's not NULL.

    What do you mean by putting together? I'm not really doing that... just want to store a time (not a date) in a field with any appropriate type in the database.

    And also I don't understand what you mean by supplying just 1 field? Do you mean Date and Time together? - I don't want to do that if it can be avoided, because the information isn't always necessarily related.

    Thanks for your reply, sorry if I didn't understand, and if you have any more ideas I would appreciate it!

    Edit: I just noticed something else, when I debug the stored procedure, it works fine with the time supplied being '19:00'. Also it works when I manually insert a record with Analyser and supply a time of '19:00'
    But it is definately not an application error.

    Also I tried using this CONVERT in the stored procedure:

    Code:
    SET @ConvertedTime = CONVERT(datetime, @EventTime, 108)
    but that didn't work either (same error)

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Because of your field name I thought you had 2 field to support date/time capture...

    Where are yo udoing this from?

    When I do this in QA, I get

    Code:
    DECLARE @x datetime
    SELECT @x = '7:00:00 PM'
    SELECT @x
    
    SELECT @x = '#7:00:00 PM#'
    SELECT CONVERT(datetime,@x,108)
    
    -- I Get
    -- ------------------------------------------------------ 
    -- 1900-01-01 19:00:00.000
    --
    -- (1 row(s) affected)
    --
    -- Server: Msg 241, Level 16, State 1, Line 10
    -- Syntax error converting datetime from character string.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Mar 2004
    Posts
    35
    I'm doing this from VS.NET (ASP.NET Application)
    Yes I do have 2 fields to store date/time information, for
    several reasons. But I'm not worried about the date, that is working fine.
    The CONVERT was only a try, because I thought it might be a formatting error.

    So basically I try to do the following:

    -Add a time (CDate) as an Sqlparameter of type datetime
    -Execute the procedure

    Then I get the error.

    The parameters are first added to a collection, to construct an
    SQL Server Command (together with the name of the stored procedure), which is then executed.

    The procedure basically looks like this:
    Code:
    ALTER PROCEDURE sp_Events_AddEvent
    @EventName varchar(30),
    @EventDate datetime,
    @EventTime datetime
    AS
        SET LANGUAGE Deutsch
    
    INSERT INTO tbl_Events
        (EventName, EventDate, EventTime)
        VALUES
        (@EventName, @EventDate, @EventTime)

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Try this and see what you get...you should add error handling anyway

    Code:
    ALTER PROCEDURE sp_Events_AddEvent
    @EventName varchar(30),
    @EventDate varchar(100),
    @EventTime varchar(100)
    AS
        SET LANGUAGE Deutsch
    
    Declare @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int
    
    IF ISDATE(@EventDate) <> 1 OR ISDATE(@EventTime) <> 1
      BEGIN
    	Select @Error_Loc = 1
    	Select @Error_Type = 50002
    	Select @Error_Message = 'Event Date or Time is not a valid value.'
    		+ ' Date Value is: ' + '"'+ CONVERT(varchar(26),ISNULL(@EventDate,'NULL') + '"'
    		+ ' Time Value is: ' + '"'+ CONVERT(varchar(26),ISNULL(@EventTime,'NULL') + '"'
    	GOTO isp_Error
      END
    
    INSERT INTO tbl_Events
        (EventName, EventDate, EventTime)
        VALUES
        (@EventName, CONVERT(datetime,@EventDate), CONVERT(datetime,@EventTime))
    
    SELECT @Result_Count = @@ROWCOUNT, @error_out = @@error
    
    If @Error_Out <> 0
      BEGIN
    	Select @Error_Loc = 2
    	Select @Error_Type = 50001
    	GOTO isp_Error
      END
    
    If @Result_Count = 0
      BEGIN
    	SELECT @Error_Loc = 2
    	SELECT @Error_Message = 'An INSERT Attempt Occured but No rows were INSERTED '
    			+ ' For Event ' + @EventName
    	SELECT @Error_Type = 50002
    	GOTO isp_Error
      END
    
    isp_Exit:
    RETURN @result	
    
    isp_Error:
    
      BEGIN
    	Select @Error_Message = 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc)) 
    		                + ',"' + ' Severity:  UserLevel ' 
    			          + ',"' + ' Message: ' + ',"' + RTrim(@Error_Message)
      END
    
    RAISERROR @Error_Type @Error_Message
    
    GOTO isp_Exit
    GO
    Last edited by Brett Kaiser; 08-06-04 at 17:58.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Aug 2004
    Posts
    8
    AFAIK, if you convert a time with no date to a DateTime in .NET, it sets the date part to 1/1/1. Double check the date part of your DateTime in the debugger.

Posting Permissions

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