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

    Question

  • Hi,

    Is there a way to catch the exeption (SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM) with in SQL Server, so it does not need to be caught in the code calling the procedure. E.g. I do not want to send null to the stored procedure.

     

    e.g. The stored procedure should be able to catch dates that are outside the range 1/1/1975 and 12/31/9999 and change them to the limits.

    E.g.

    IF @StartDate < CONVERT(DateTime, '01/01/1753 12:00:00')

    BEGIN

        SET @StartDate = CONVERT(DateTime, '01/01/1753 12:00:00')

    END

    IF @EndDate > CONVERT(DateTime, '01/01/1753 11:59:59')

    BEGIN

        SET @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')

    END

     

    This still throws an exception because the StartDate or EndDate when tested fail the condition.

    Tuesday, June 27, 2006 12:44 AM

Answers

  • You cannot validate the input parameters until you are in to the stored procedure.  Your only options are to modify the input parameters so you can validate (by changing the data type to varchar), OR valid the out of range problem in your front end code (whatever is calling the stored procedure).
    Wednesday, June 28, 2006 1:15 AM

All replies

  • You can use the IsDate function to determine if it is a valid date.  IsDate returns 0 for invalid dates and 1 for valid dates, so something like this may work for you.

    Declare @StartDate VarChar(20)

    Declare @EndDate VarChar(20)

    Set @StartDate = '01/01/1740'

    Set @EndDate = '1/1/10000'

    If Not IsDate(@StartDate) = 1

    Set @StartDate = Convert(DateTime, '01/01/1753 12:00:00')

    If Not IsDate(@EndDate) = 1

    Set @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')

     

    Select IsDate(@StartDate), @StartDate, @EndDate

    Tuesday, June 27, 2006 2:15 AM
  • declare @STARTDATE DATETIME

    DECLARE @ENDDATE DATETIME

    SELECT @STARTDATE=CONVERT(DateTime, '01/01/1753 12:00:00')

    SELECT @ENDDATE=CONVERT(DateTime, '12/31/9999 11:59:59')

    SELECT @STARTDATE=DATEADD(DD,-1,@STARTDATE)

    IF @@ERROR <>0

    BEGIN

    SET @StartDate = CONVERT(DateTime, '01/01/1753 12:00:00')

    select 'INVALID MINIMUM TIME'AS MSG

    END

    SELECT @ENDDATE=DATEADD(DD,1,@ENDDATE)

    IF @@ERROR<>0

    BEGIN

    select 'INVALID MAXIMUM TIME'

    SET @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')

    END

    SELECT @startdate,@ENDDATE

    Tuesday, June 27, 2006 2:29 AM
  • Hi,

    Am trying to avoid changing the declaration section, also tried changing it to varChar, but the invalid date range exception seems to be comming up all the time regardless of the changes.

    ALTER PROCEDURE [dbo].[GetAssessmentBookingsUsingParameters]

    @AssessmentItemID int,

    @StartDate DateTime,

    @EndDate DateTime,

    @SearchType Int,

    @UserID Int,

    @VenueID Int,

    @PortalID Int

    He I just noticed you have a bible quote at the bottom of your screen name. I got one too. It is one God gave me. seven or eight people all in a row in seperate churches. Prayed the verse of scripture from "I know the thoughts I think toward you says the Lord for good and not for evil." - Jesus is really cool. So why is the verse you included special to you?

    Tuesday, June 27, 2006 10:39 PM
  • You cannot validate the input parameters until you are in to the stored procedure.  Your only options are to modify the input parameters so you can validate (by changing the data type to varchar), OR valid the out of range problem in your front end code (whatever is calling the stored procedure).
    Wednesday, June 28, 2006 1:15 AM
  • Hi,

    Please help me . i have same exception

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

     

    i  am entering value from frontend and when it check with database it throws an exception

    if i do not enter value from frontend then also it throws exception

     

    Stored Procedure is not giving any error

    do you have any idea how to solve this problem

     

    ALTER PROCEDURE [dbo].[usp_SearchAdvance]

    (@EnteredDateFrom datetime ='01/01/1900',

    @EnteredDateTo datetime ='01/01/1900',

    @JobPostalCode varchar(10) = '00000',

    @DateReceivedFrom datetime = '01/01/1900',

    @DateReceivedTo datetime = '01/01/1900',

    @PostalCode varchar(10) = '00000',

    @JobTypeProj varchar(20) = 'SELECT',

    @LeadSourceProj varchar(20) = 'SELECT',

    @ReferredByProj varchar(20) = 'SELECT')

    SET NOCOUNT ON;

     

    DECLARE @sqlSTR VARCHAR(4000)

    DECLARE @whereSTR VARCHAR(4000)

     

    set @sqlSTR = 'SELECT dbo.Contacts.TrackNumber, dbo.Contacts.FirstName, dbo.Contacts.LastName, dbo.Contacts.Address, dbo.Contacts.Company

    FROM dbo.Contacts INNER JOIN dbo.Projects ON dbo.Contacts.ContactID = dbo.Projects.ContactID '

    --Checking NullValue For All Passing Parameter

    set @whereSTR = ' WHERE '

    set @isAND = 0

    -- Projects Date Panel

    IF @EnteredDateFrom != '01/01/1900' and @EnteredDateTo != '01/01/1900'

    BEGIN

    set @whereSTR = @whereSTR + ' Projects.EnteredDate >= ' + CHAR(39) + CAST(@EnteredDateFrom as varchar(20)) + CHAR(39) + ' AND Projects.EnteredDate <= ' + CHAR(39) + CAST(@EnteredDateTo AS varchar(20)) + CHAR(39)

     

    END

    Monday, June 02, 2008 2:29 PM
  • I just ran accross this error and the IsDate worked for me.  When setting the params, in preparation for the query execution I added an if statement and it took care of my issues.  It should be noted that for my situation, setting the end date as the start date was appropriate.<code>

            Dim parameterEnd_Date As New SqlParameter("@end_date", SqlDbType.DateTime, 8)
            parameterEnd_Date.Value = Cal_End_Date.SelectedDate
            If Not IsDate(parameterEnd_Date) Then
                parameterEnd_Date.Value = parameterEvent_Date.Value
            End If</code>
    Tuesday, November 18, 2008 7:19 PM