Tuesday, June 27, 2006 12:44 AM
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.
IF @StartDate < CONVERT(DateTime, '01/01/1753 12:00:00')
BEGINSET @StartDate = CONVERT(DateTime, '01/01/1753 12:00:00')
IF@EndDate > CONVERT(DateTime, '01/01/1753 11:59:59')
BEGINSET @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')
This still throws an exception because the StartDate or EndDate when tested fail the condition.
Tuesday, June 27, 2006 2:15 AM
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.
Set@StartDate = '01/01/1740'
Set@EndDate = '1/1/10000'
IfNot IsDate(@StartDate) = 1 Set @StartDate = Convert(DateTime, '01/01/1753 12:00:00')
IfNot IsDate(@EndDate) = 1 Set @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')
SelectIsDate(@StartDate), @StartDate, @EndDate
Tuesday, June 27, 2006 2:29 AM
SELECT@STARTDATE=CONVERT(DateTime, '01/01/1753 12:00:00')
SELECT@ENDDATE=CONVERT(DateTime, '12/31/9999 11:59:59')
BEGINSET @StartDate = CONVERT(DateTime, '01/01/1753 12:00:00') select 'INVALID MINIMUM TIME'AS MSG
BEGINselect 'INVALID MAXIMUM TIME' SET @EndDate = CONVERT(DateTime, '12/31/9999 11:59:59')
Tuesday, June 27, 2006 10:39 PM
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.
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?
Wednesday, June 28, 2006 1:15 AMYou 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).
Monday, June 02, 2008 2:29 PM
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
ALTERPROCEDURE [dbo].[usp_SearchAdvance] (@EnteredDateFrom datetime ='01/01/1900',
@JobPostalCodevarchar(10) = '00000',
@DateReceivedFromdatetime = '01/01/1900',
@DateReceivedTodatetime = '01/01/1900',
@PostalCodevarchar(10) = '00000',
@JobTypeProjvarchar(20) = 'SELECT',
@LeadSourceProjvarchar(20) = 'SELECT',
@ReferredByProjvarchar(20) = 'SELECT')
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
Tuesday, November 18, 2008 7:19 PMI 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.SelectedDateIf Not IsDate(parameterEnd_Date) ThenparameterEnd_Date.Value = parameterEvent_Date.ValueEnd If</code>