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

    Friday, August 06, 2010 6:45 AM

Answers

  •   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, esquel@sommarskog.se
    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
    Friday, August 06, 2010 9:11 AM
  • > 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, esquel@sommarskog.se
    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
    Friday, August 06, 2010 11:17 AM
  • 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.

    Friday, August 06, 2010 11:40 AM

All replies

  • 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?

    Friday, August 06, 2010 7:03 AM
  • > 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, esquel@sommarskog.se
    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
    Friday, August 06, 2010 7:14 AM
  •   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','sravani.lbmwsa@gmail.com','srawni_lb@yahoo.co.in','1277312','277334224','23248976','dfgdf','evbrwe',6,'ghfg','6546','dfsxxf@gxh.ixk','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!..........



    Friday, August 06, 2010 7:30 AM
  •   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, esquel@sommarskog.se
    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
    Friday, August 06, 2010 9:11 AM
  • 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
    Friday, August 06, 2010 9:22 AM
  • Your ADO code is SMALLDATETIME...
    Friday, August 06, 2010 10:20 AM
  •  

    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

    Friday, August 06, 2010 11:03 AM
  • > 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, esquel@sommarskog.se
    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
    Friday, August 06, 2010 11:17 AM
  • 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.

    Friday, August 06, 2010 11:40 AM