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

Question

just i'm storing date like 12/12/1986 in to smalldatatime in database using front end(vb) only i'm getting this error.

 

Pls Help me.................

Answer 1

12.12.1986 should work for both, datetime and smalldatetime.

Are you shure, the error is with this date? Have you use the profiler to suffer out, which command is sended to SQL Server? 

Which SQL Version are you using?

Answer 2

> just i'm storing date like 12/12/1986 in to smalldatatime in database using front end(vb) only i'm getting this error.

To start with, SqlDateTime is an error from ADO .Net, so this has nothing to do with T-SQL.

Second, whereever you ask your question, please post your code, else we can't tell what you have done wrong.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 3

  frontend:          Ict.Ds.AddParameterToSQL(mySqlCommand, "@dob", SqlDbType.SmallDateTime, 4, ParameterDirection.Input, myCandidateDetails.dob)

errorline:  mySqlDataReader = mySqlCommand.ExecuteReader()


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



 storedprocedure: PRINT 'Creating procedure pa_candidate
...'
GO
IF OBJECTPROPERTY(object_id('pa_candidate'),'IsProcedure') = 1
   DROP PROCEDURE pa_candidate
GO
CREATE PROCEDURE pa_candidate
@firstName varchar(32) = Null,
@middleName varchar(32) = NULL, 
@lastName varchar(32) = NULL, 
@ssnNo varchar(20) = NULL,
@dob smalldatetime=NULL,
@primaryEmailId varchar(64)=Null,
@secondaryEmailId varchar(64)=NULL,
@homeNumber varchar(10)=Null,
@mobNumber varchar(10)=NULL,
@alternativeNumber varchar(10)=NULL,
@department varchar(64)=NULL,
@currentEmployer varchar(128)=NULL,
@roleId smallint = NULL,
@contactPerson varchar(64)=NULL,
@phone varchar(10) = NULL,
@emailId varchar(64) = NULL,
@visa varchar(10)=NULL,
@locationId smallint =NULL,
@relocation varchar(10) = NULL,
@availability varchar(32) = NULL,
@comments varchar(128) = NULL,
@primarySkillId smallint = NULL,
@secSkillId smallint = NULL,
@rate varchar(10) = NULL,
@hourlyRate varchar(10) = NULL,
@resumeTitle varchar(128) = NULL,
@currentStage varchar(32) = NULL
AS
BEGIN
   SET NOCOUNT ON
   DECLARE @errorMessage varchar(512),@candidateId  int
   SELECT @errorMessage = ''

   IF ISNULL(@firstName, '') = ''     
      SELECT @errorMessage = @errorMessage + '* First Name can not be empty.'      
   IF ISNULL(@lastName,'')=''
      SELECT @errorMessage = @errorMessage + '* Last Name can not be empty.' 
   IF ISNULL(@mobNumber, '') = ''
      SELECT @errorMessage = @errorMessage + '* Mobile Number can not be empty.' 
   IF ISNULL(@primarySkillId,'') = ''     
      SELECT @errorMessage = @errorMessage + '* Primary Skill Id can not be empty.' 
   IF ISNULL(@hourlyRate, '') = ''     
      SELECT @errorMessage = @errorMessage + '* Hourly Rate can not be empty.'
   IF ISNULL(@resumeTitle, '') = ''     
      SELECT @errorMessage = @errorMessage + '* Resume Title can not be empty.'   

      IF ISNULL(@errorMessage,'') <> ''
      BEGIN
        SELECT CONVERT(int, -1), @errorMessage
        RETURN
      END

    IF EXISTS ( SELECT 1 FROM "candidate" WHERE mobNumber = @mobNumber)
      BEGIN
        SELECT @errorMessage = @errorMessage + ' *Specified  mobile Number already exists. Please Specify Another..'
        SELECT CONVERT(int, -1), @errorMessage
        RETURN
      END
    IF EXISTS ( SELECT 1 FROM "candidate" WHERE primaryEmailId = @primaryEmailId)
      BEGIN
        SELECT @errorMessage = @errorMessage + ' *Specified Email Id already exists. Please Specify Another..'
        SELECT CONVERT(int, -1), @errorMessage
        RETURN
      END

BEGIN TRAN
      SELECT @candidateId = ISNULL(MAX(candidateId),0) + 1
         FROM "candidate"  WITH (HOLDLOCK) 

  INSERT "candidate" (candidateId,firstName,middleName,lastName,ssnNo,dob,primaryEmailId,secondaryEmailId,homeNumber,mobNumber,alternativeNumber,department,currentEmployer,roleId,contactPerson,phone,emailId,visa,locationId,relocation,availability,comments,primarySkillId,secSkillId,rate,hourlyRate,resumeTitle,currentStage)
     SELECT @candidateId,@firstName,@middleName,@lastName,@ssnNo,@dob,@primaryEmailId,@secondaryEmailId,@homeNumber,@mobNumber,@alternativeNumber,@department,@currentEmployer,@roleId,@contactPerson,@phone,@emailId,@visa,@locationId,@relocation,@availability,@comments,@primarySkillId,@secSkillId,@rate,@hourlyRate,@resumeTitle,@currentStage

  IF @@ERROR = 0 AND @@ROWCOUNT = 1
        BEGIN
          COMMIT TRAN
          SELECT @candidateId, 'Candidate Details has been successfully added.  '
        END
     ELSE
        BEGIN
          ROLLBACK TRAN
       err:        
            SELECT CONVERT(int, -1), 'Unable to add Candidate Details.   '
            RETURN
        END
END
GO
--Exec pa_candidate 'sravaani','jdfdsfs','dafsd','4456','12/12/2009','[e-mail]','[e-mail]','1277312','277334224','23248976','dfgdf','evbrwe',6,'ghfg','6546','[e-mail]','false',8,'yees','ffddsf','dsfsfgdf ggfgdg fdtg fytrdg',2,6,5,5,'sravanrri',5


table:create table candidate (candidateId int,firstName varchar(32) not null,middleName varchar(32),lastName varchar(32) not null,ssnNo varchar(20),dob smalldatetime,primaryEmailId varchar(64),
secondaryEmailId varchar(64),homeNumber varchar(10),mobNumber varchar(10) not null,alternativeNumber varchar(10),department varchar(64),currentEmployer varchar(128),
roleId smallint,contactPerson varchar(64),phone varchar(10),emailId varchar(64),visa varchar(10),locationId smallint,relocation varchar(10),
availability varchar(32),comments varchar(128),primarySkillId smallint not null,secSkillId smallint,rate varchar(10), hourlyRate varchar(10) not null,resumeTitle varchar(128) not null,
currentStage varchar(32),
constraint PK_candidate primary key  (candidateId))




Help Me!..........

Answer 4

  frontend:          Ict.Ds.AddParameterToSQL(mySqlCommand, "@dob", SqlDbType.SmallDateTime, 4, ParameterDirection.Input, myCandidateDetails.dob)

errorline:  mySqlDataReader = mySqlCommand.ExecuteReader()


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

You can skip the T-SQL code. What is interesting is what you have in myCandidateDetails.dob. And for that matter what data type that field has.

It can also be relevant with what's in this AddParameterToSQL, which I suspect is part of some local framework (it's nothing I recognize).

Beside at looking at myCandidateDetails.dob, you can also look at what is in mySqlCommand.Parameters[4].

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 5

Hi,

A DateTime is a value type and if you do not set it within your application it will default. I would check what value you are putting in there, if indeed you are.

DateTime Structure
http://msdn.microsoft.com/en-us/library/system.datetime.aspx

 

---

Jon

Answer 6

Your ADO code is SMALLDATETIME...

Answer 7

 

candidateDetails class:

i declared like this:       Private _dob As Nullable(Of System.DateTime)

                                 Public Property dob() As Nullable(Of System.DateTime)
                                 Get
                                        Return _dob
                                 End Get
                                 Set(ByVal value As Nullable(Of System.DateTime))
                                        _dob = value
                                 End Set
                                 End Property

 

with your answer i found one thing here is : i'm not declared dob as smalldatetime here but i tried to give like that but it' s not taking system.smalldatetime

 

so give any suggestions on that

Answer 8

> with your answer i found one thing here is : i'm not declared dob as smalldatetime here but i tried to give like that but it' s not taking system.smalldatetime

We are straying more and more from the scope of this forum.  You problem is strictly .Net, so I recommend that you look through list of forums, and find one that is devoted to data access from .Net.

It is important to understand that System.Datetime has no direct relation to the datetime data type in SQL Server. That is, .Net has its type and SQL Server has its types. The datetime data type in SQL Server has the range 1753-01-01 to 9999-12-31. I don't know exactly where System.DateTime starts, but I would guess 0001-01-01.

And the type smalldatetime is unique to SQL Server; it has no correspondance in .Net as far as I know. The smalldatetime data type permits a range of 1900-01-01 to a date somewhere in 2076.

All that said, I would expect your code to work, as long as the date is within the range for smalldatetime. So I suspect that the input date is not correctly interpreted somewhere. You should be able to determine this using the debugger in Visual Studio.

---

Erland Sommarskog, SQL Server MVP, [e-mail]
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Answer 9

thanks for spend with me today. i got the answer with your support.

when i search in controller first i declare the below code without nullable. when u told that once see in mycandidateDetails.dob only, i identify that. datetime should declare with nullable  i think

                                 Private _dob As Nullable(Of System.DateTime)

                                 Public Property dob() As Nullable(Of System.DateTime)
                                 Get
                                        Return _dob
                                 End Get
                                 Set(ByVal value As Nullable(Of System.DateTime))
                                        _dob = value
                                 End Set
                                 End Property

 

 

any way i got the answer. thank u very much.