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.

Answer 1

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

Answer 2

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

Answer 3

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?

Answer 4

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).

Answer 5

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

Answer 6

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>