none
error converting DateTime to SQL Date column RRS feed

  • Question

  • I am trying to insert a row in a table that uses a SQL Date column as part of its primary key. I am creating a new entity and populating the DateTime property on it from a value selected from a DateTimePicker:

    TaxPmt newtax = new TaxPmt();
    newtax.AsOfDate = dtpAsOfDate.Value.Date;
    myContext.AddToTaxPmt(newtax);
    myContext.SaveChanges();

    But I get this error when i try to .SaveChanges():

    " The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value"

    The value coming from the dtpAsOfDate.Value.Date is the date I want, but it includes the default time value of 12:00AM. I don't know if that's what's causing my problem or what.

    Any ideas?

    Wednesday, October 12, 2011 8:36 PM

Answers

  • Brad,

    I really would like to see the sql that is getting sent when you attempt to do the update.  Can you run SQL Profiler on your database and capture that?


    Tom Overton
    • Marked as answer by BradInDallas Thursday, October 13, 2011 6:37 PM
    Thursday, October 13, 2011 5:49 PM
  • Actually, it's not the AsOfDate that's the problem, it's the dtCreate.  It's defaulting it to '0001-01-01 00:00:00' and this will cause an error:

    declare @TaxPmt as table
    (
    dtCreate datetime
    )
    
    declare @dtCreate datetime2(7)
    
    set @dtCreate = '0001-01-01 00:00:00'
    
    insert @TaxPmt
    (dtCreate)
    select @dtCreate
    

    Try to populate a value for the dtCreate property on your entity and I think it will work.


    Tom Overton
    • Marked as answer by BradInDallas Thursday, October 13, 2011 6:57 PM
    Thursday, October 13, 2011 6:55 PM

All replies

  • Try just sending it a pure date format.  Like this:

    newtax.AsOfDate = dtpAsOfDate.Value.ToShortDateString;
    

     


    Tom Overton
    Wednesday, October 12, 2011 8:48 PM
  • Tom, it won't let me implicitly cast from a string to a dateTime.
    Wednesday, October 12, 2011 8:55 PM
  • What about without the Date part:

    newtax.AsOfDate = dtpAsOfDate.Value;

     


    Tom Overton
    Wednesday, October 12, 2011 9:01 PM
  • Hi BradInDallas;

    SQL server datatype datetime2 is equivalent to CLR .Net DataTime datatype so try this :

    newtax.AsOfDate = dtpAsOfDate.Value;

    Seeming that the Value property of the DateTimePicker returns a DateTime datatype.


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Wednesday, October 12, 2011 9:04 PM
  • My SQL Server datatype is defined as Date by itself.

    newtax.AsOfDate = dtpAsOfDate.Value;

    this throws the same error " The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value"




    • Edited by BradInDallas Wednesday, October 12, 2011 9:18 PM
    Wednesday, October 12, 2011 9:16 PM
  • Hi BradInDallas;

    I was going with the error message that you posted, "" The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value", seeming that the CLR .Net framework does not have a data type of datetime2 as stated in the error message I can only assume that it was pointing to the database and not the value in the application / EF model.

    Can you please post the class from the EF model called TaxPmt so we can see what is happening?

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, October 13, 2011 4:44 AM
  •         public global::System.DateTime AsOfDate
            {
                get
                {
                    return this._AsOfDate;
                }
                set
                {
                    this.OnAsOfDateChanging(value);
                    this.ReportPropertyChanging("AsOfDate");
                    this._AsOfDate = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
                    this.ReportPropertyChanged("AsOfDate");
                    this.OnAsOfDateChanged();
                }
            }
            private global::System.DateTime _AsOfDate;
            partial void OnAsOfDateChanging(global::System.DateTime value);
            partial void OnAsOfDateChanged();
            /// <summary>
            /// There are no comments for Property dtCreate in the schema.
            /// </summary>
            [global::System.Data.Objects.DataClasses.EdmScalarPropertyAttribute(IsNullable=false)]
            [global::System.Runtime.Serialization.DataMemberAttribute()]
    

    Thursday, October 13, 2011 1:21 PM
  • This is from my .edmx storage model:

    <EntityType Name="TaxPmt"> <Key> <PropertyRef Name="PayeeCode" /> <PropertyRef Name="TaxID" /> <PropertyRef Name="TaxYear" /> <PropertyRef Name="RType" /> <PropertyRef Name="AsOfDate" /> </Key> <Property Name="PayeeCode" Type="decimal" Nullable="false" Precision="10" /> <Property Name="TaxID" Type="varchar" Nullable="false" MaxLength="50" /> <Property Name="TaxYear" Type="int" Nullable="false" /> <Property Name="RType" Type="nchar" Nullable="false" MaxLength="10" /> <Property Name="AsOfDate" Type="date" Nullable="false" /> <Property Name="dtCreate" Type="datetime" Nullable="false" /> <Property Name="CreatedBy" Type="nchar" MaxLength="30" /> <Property Name="LastUpdatedBy" Type="nchar" MaxLength="50" /> <Property Name="AmtPaid" Type="money" /> <Property Name="DatePaid" Type="date" /> <Property Name="P&amp;I" Type="money" /> <Property Name="Penalties" Type="money" /> <Property Name="Interest" Type="money" /> <Property Name="CollectionFees" Type="money" /> <Property Name="OtherFees" Type="money" /> <Property Name="Comments" Type="varchar(max)" /> <Property Name="Balance" Type="money" /> <Property Name="dtLastUpdate" Type="datetime" /> <Property Name="45DayRedemptionAmt" Type="money" /> <Property Name="NextCriticalEvent" Type="nchar" MaxLength="10" /> <Property Name="NCEDate" Type="date" /> </EntityType>

    And this is from my conceptual model:
    <EntityType Name="TaxPmt">
              <Key>
                <PropertyRef Name="PayeeCode" />
                <PropertyRef Name="TaxID" />
                <PropertyRef Name="TaxYear" />
                <PropertyRef Name="RType" />
                <PropertyRef Name="AsOfDate" /></Key>
              <Property Name="PayeeCode" Type="Decimal" Nullable="false" />
              <Property Name="TaxID" Type="String" Nullable="false" />
              <Property Name="TaxYear" Type="Int32" Nullable="false" />
              <Property Name="RType" Type="String" Nullable="false" />
              <Property Name="AsOfDate" Type="DateTime" Nullable="false" />
              <Property Name="dtCreate" Type="DateTime" Nullable="false" />
              <Property Name="CreatedBy" Type="String" Nullable="true" />
              <Property Name="LastUpdatedBy" Type="String" Nullable="true" />
              <Property Name="AmtPaid" Type="Decimal" Nullable="true" />
              <Property Name="DatePaid" Type="DateTime" Nullable="true" />
              <Property Name="P_I" Type="Decimal" Nullable="true" />
              <Property Name="Penalties" Type="Decimal" Nullable="true" />
              <Property Name="Interest" Type="Decimal" Nullable="true" />
              <Property Name="CollectionFees" Type="Decimal" Nullable="true" />
              <Property Name="OtherFees" Type="Decimal" Nullable="true" />
              <Property Name="Comments" Type="String" Nullable="true" />
              <Property Name="Balance" Type="Decimal" Nullable="true" />
              <Property Name="dtLastUpdate" Type="DateTime" Nullable="true" />
              <Property Name="C45DayRedemptionAmt" Type="Decimal" Nullable="true" />
              <Property Name="NextCriticalEvent" Type="String" Nullable="true" />
              <Property Name="NCEDate" Type="DateTime" Nullable="true" />
              <NavigationProperty Name="Payee" Relationship="TAXTESTModel.FK_TaxPmt_Payee" FromRole="TaxPmt" ToRole="Payee" />
              <NavigationProperty Name="TaxAmt" Relationship="TAXTESTModel.TaxAmtTaxPmt" FromRole="TaxPmt" ToRole="TaxAmt" /></EntityType><br/>
    



    Thursday, October 13, 2011 1:27 PM
  • Hi BradInDallas;

    I just created a test database with three fields Id int, Name nvarchar and AsOfDate date and in a test project updated the date field with a DateTimePicker as you did in your first post and it updated the db with no issues. There must be something out of sync in the project. Can you build a new test project create a edmx and try doing the same thing to see if it is working.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, October 13, 2011 3:17 PM
  • Hi guys,

    Fernando, I did that exact same test last night and couldn't reproduce.  I changed the database column to be date, datetime, and datetime2 and couldn't get it to break.  Even when I left the model out of sync and changed the database column it still wouldn't give an error.   

    I agree with Fernando's suggestion, start from scratch and create a simple edmx with that one table and see if it updates okay. 

    Brad, can you also post the DDL of the database table? 

    Thanks,

    Tom 


    Tom Overton
    Thursday, October 13, 2011 3:24 PM
  • Here's the create stmt for my table. I'm running EF 3.5 VS 2008 if that makes any difference.

    USE [TAXTEST]
    GO
    
    /****** Object:  Table [dbo].[TaxPmt]    Script Date: 10/13/2011 10:23:17 ******/
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[TaxPmt](
    	[PayeeCode] [decimal](10, 0) NOT NULL,
    	[TaxID] [varchar](50) NOT NULL,
    	[TaxYear] [int] NOT NULL,
    	[RType] [nchar](10) NOT NULL,
    	[AsOfDate] [date] NOT NULL,
    	[dtCreate] [datetime] NOT NULL,
    	[CreatedBy] [nchar](30) NULL,
    	[LastUpdatedBy] [nchar](50) NULL,
    	[AmtPaid] [money] NULL,
    	[DatePaid] [date] NULL,
    	[P&I] [money] NULL,
    	[Penalties] [money] NULL,
    	[Interest] [money] NULL,
    	[CollectionFees] [money] NULL,
    	[OtherFees] [money] NULL,
    	[Comments] [varchar](max) NULL,
    	[Balance] [money] NULL,
    	[dtLastUpdate] [datetime] NULL,
    	[45DayRedemptionAmt] [money] NULL,
    	[NextCriticalEvent] [nchar](10) NULL,
    	[NCEDate] [date] NULL,
     CONSTRAINT [PK_TaxPmt] PRIMARY KEY CLUSTERED 
    (
    	[PayeeCode] ASC,
    	[TaxID] ASC,
    	[TaxYear] ASC,
    	[RType] ASC,
    	[AsOfDate] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO
    
    ALTER TABLE [dbo].[TaxPmt]  WITH CHECK ADD  CONSTRAINT [FK_TaxPmt_Payee] FOREIGN KEY([PayeeCode], [RType])
    REFERENCES [dbo].[Payee] ([PayeeCode], [Reporting])
    GO
    
    ALTER TABLE [dbo].[TaxPmt] CHECK CONSTRAINT [FK_TaxPmt_Payee]
    GO
    
    ALTER TABLE [dbo].[TaxPmt]  WITH CHECK ADD  CONSTRAINT [FK_TaxPmt_TaxAmt] FOREIGN KEY([PayeeCode], [TaxID], [TaxYear], [RType])
    REFERENCES [dbo].[TaxAmt] ([PayeeCode], [TaxID], [TaxYear], [RType])
    ON UPDATE CASCADE
    ON DELETE CASCADE
    GO
    



    Thursday, October 13, 2011 3:28 PM
  • Thanks Brad.

    I don't see anything in the DDL that stands out.  It's what I expected.  ARe there any triggers on the table?

    Perhaps this is a bug of some type in EF 3.5.  I don't have EF 3.5 so can't try a test scenario in EF 3.5 unfortunately. 


    Tom Overton
    Thursday, October 13, 2011 3:34 PM
  • Should I try changing the data type on my storage model to datetime and leave the Db table as-is?
    Thursday, October 13, 2011 3:36 PM
  • It looks like it's already System.DateTime in the model.  You could try changing the database table to datetime, if you're able to do that without breaking any other code that use that table. 


    Tom Overton
    Thursday, October 13, 2011 3:45 PM
  • I think it's datetime in the conceptual model but Date in the storage model. Is that not correct?
    Thursday, October 13, 2011 3:46 PM
  • Yes, it will be datetime in the model even if it's date in the database.  EF is supposed to be able to handle the formatting when it does the table update but something is going wrong there obviously.

    Can you use SQL Server Profiler to view the SQL that is getting sent to the database when the SaveChanges is done? 

     


    Tom Overton
    Thursday, October 13, 2011 4:01 PM
  •  

    Have you tried to build a simple project and do the small test?

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, October 13, 2011 4:08 PM
  • Not yet. I'm afraid of how long that will take, and I'm not sure what I will do next if it works. It took me countless hours just to get my model to build after I first updated it from the database.

    Would it make sense to try changing this line in my storage model:

              <Property Name="AsOfDate" Type="date" Nullable="false" />

    To this:
            <Property Name="AsOfDate" Type="datetime" Nullable="false" />

    Thursday, October 13, 2011 4:16 PM
  • Yes, you could try changing the SSDL and see if that makes a difference. 
    Tom Overton
    Thursday, October 13, 2011 4:32 PM
  • Hi Brad;
    To your question,

    Would it make sense to try changing this line in my storage model:

    <PropertyName="AsOfDate"Type="date"Nullable="false"/>

    To this:
    <PropertyName="AsOfDate"Type="datetime"Nullable="false"/>


    No, because the Storage must match what the database has.

    When I asked you to create a test project I did not mean to bring the complete code from your current project to the new project I mean for you to create a project with one form and one button and on DateTimePicker. In the Button click event write the code to create and entity fill the fields add it to object context of which you also need to create with just the one table. Should not take more then 10 minutes.

     


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, October 13, 2011 4:32 PM
  • OK, I

    1. created a test Windows forms project
    2. added an ADO.NET entity model,
    3. added one entity from the database (my TaxPmt entity)
    4. dropped a DateTimePicker onto my blank form
    5. added a valuechanged event to the dtp
    6. in my valuechanged event I created a new TaxPmt object and set all properties to hard-coded (valid) values. At first I set the AsOf date value to my datetimepicker.Value, but got the same error when I call .SaveChanges(). So I commented out that code and just set the AsOfDate = System.DateTime.Today. Same error. I moved all my code from the valuechaged event to the form init (to rule out any issue with the datetimepicker), but same error.

     

     

            public global::System.DateTime AsOfDate
            {
                get
                {
                    return this._AsOfDate;
                }
                set
                {
                    this.OnAsOfDateChanging(value);
                    this.ReportPropertyChanging("AsOfDate");
                    this._AsOfDate = global::System.Data.Objects.DataClasses.StructuralObject.SetValidValue(value);
                    this.ReportPropertyChanged("AsOfDate");
                    this.OnAsOfDateChanged();
                }
            }
            private global::System.DateTime _AsOfDate;
            partial void OnAsOfDateChanging(global::System.DateTime value);
            partial void OnAsOfDateChanged();
    My whole .edmx file:

    <pre lang="x-xml"><?xml version="1.0" encoding="utf-8"?> <edmx:Edmx Version="1.0" xmlns:edmx="http://schemas.microsoft.com/ado/2007/06/edmx"> <!-- EF Runtime content --> <edmx:Runtime> <!-- SSDL content --> <edmx:StorageModels> <Schema Namespace="TAXTESTModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2006/04/edm/ssdl"> <EntityContainer Name="TAXTESTModelStoreContainer"> <EntitySet Name="TaxPmt" EntityType="TAXTESTModel.Store.TaxPmt" store:Type="Tables" Schema="dbo" /> </EntityContainer> <EntityType Name="TaxPmt"> <Key> <PropertyRef Name="PayeeCode" /> <PropertyRef Name="TaxID" /> <PropertyRef Name="TaxYear" /> <PropertyRef Name="RType" /> <PropertyRef Name="AsOfDate" /> </Key> <Property Name="PayeeCode" Type="decimal" Nullable="false" Precision="10" /> <Property Name="TaxID" Type="varchar" Nullable="false" MaxLength="50" /> <Property Name="TaxYear" Type="int" Nullable="false" /> <Property Name="RType" Type="nchar" Nullable="false" MaxLength="10" /> <Property Name="AsOfDate" Type="date" Nullable="false" /> <Property Name="dtCreate" Type="datetime" Nullable="false" /> <Property Name="CreatedBy" Type="nchar" MaxLength="30" /> <Property Name="LastUpdatedBy" Type="nchar" MaxLength="50" /> <Property Name="AmtPaid" Type="money" /> <Property Name="DatePaid" Type="date" /> <Property Name="P&amp;I" Type="money" /> <Property Name="Penalties" Type="money" /> <Property Name="Interest" Type="money" /> <Property Name="CollectionFees" Type="money" /> <Property Name="OtherFees" Type="money" /> <Property Name="Comments" Type="varchar(max)" /> <Property Name="Balance" Type="money" /> <Property Name="dtLastUpdate" Type="datetime" /> <Property Name="45DayRedemptionAmt" Type="money" /> <Property Name="NextCriticalEvent" Type="nchar" MaxLength="10" /> <Property Name="NCEDate" Type="date" /> </EntityType> </Schema> </edmx:StorageModels> <!-- CSDL content --> <edmx:ConceptualModels> <Schema Namespace="TAXTESTModel" Alias="Self" xmlns="http://schemas.microsoft.com/ado/2006/04/edm"> <EntityContainer Name="TAXTESTEntities"> <EntitySet Name="TaxPmt" EntityType="TAXTESTModel.TaxPmt" /> </EntityContainer> <EntityType Name="TaxPmt"> <Key> <PropertyRef Name="PayeeCode" /> <PropertyRef Name="TaxID" /> <PropertyRef Name="TaxYear" /> <PropertyRef Name="RType" /> <PropertyRef Name="AsOfDate" /> </Key> <Property Name="PayeeCode" Type="Decimal" Nullable="false" Precision="10" Scale="0" /> <Property Name="TaxID" Type="String" Nullable="false" MaxLength="50" Unicode="false" FixedLength="false" /> <Property Name="TaxYear" Type="Int32" Nullable="false" /> <Property Name="RType" Type="String" Nullable="false" MaxLength="10" Unicode="true" FixedLength="true" /> <Property Name="AsOfDate" Type="DateTime" Nullable="false" /> <Property Name="dtCreate" Type="DateTime" Nullable="false" /> <Property Name="CreatedBy" Type="String" MaxLength="30" Unicode="true" FixedLength="true" /> <Property Name="LastUpdatedBy" Type="String" MaxLength="50" Unicode="true" FixedLength="true" /> <Property Name="AmtPaid" Type="Decimal" Precision="19" Scale="4" /> <Property Name="DatePaid" Type="DateTime" /> <Property Name="P_I" Type="Decimal" Precision="19" Scale="4" /> <Property Name="Penalties" Type="Decimal" Precision="19" Scale="4" /> <Property Name="Interest" Type="Decimal" Precision="19" Scale="4" /> <Property Name="CollectionFees" Type="Decimal" Precision="19" Scale="4" /> <Property Name="OtherFees" Type="Decimal" Precision="19" Scale="4" /> <Property Name="Comments" Type="String" MaxLength="Max" Unicode="false" FixedLength="false" /> <Property Name="Balance" Type="Decimal" Precision="19" Scale="4" /> <Property Name="dtLastUpdate" Type="DateTime" /> <Property Name="C45DayRedemptionAmt" Type="Decimal" Precision="19" Scale="4" /> <Property Name="NextCriticalEvent" Type="String" MaxLength="10" Unicode="true" FixedLength="true" /> <Property Name="NCEDate" Type="DateTime" /> </EntityType> </Schema> </edmx:ConceptualModels> <!-- C-S mapping content --> <edmx:Mappings> <Mapping Space="C-S" xmlns="urn:schemas-microsoft-com:windows:storage:mapping:CS"> <EntityContainerMapping StorageEntityContainer="TAXTESTModelStoreContainer" CdmEntityContainer="TAXTESTEntities"> <EntitySetMapping Name="TaxPmt"> <EntityTypeMapping TypeName="IsTypeOf(TAXTESTModel.TaxPmt)"> <MappingFragment StoreEntitySet="TaxPmt"> <ScalarProperty Name="PayeeCode" ColumnName="PayeeCode" /> <ScalarProperty Name="TaxID" ColumnName="TaxID" /> <ScalarProperty Name="TaxYear" ColumnName="TaxYear" /> <ScalarProperty Name="RType" ColumnName="RType" /> <ScalarProperty Name="AsOfDate" ColumnName="AsOfDate" /> <ScalarProperty Name="dtCreate" ColumnName="dtCreate" /> <ScalarProperty Name="CreatedBy" ColumnName="CreatedBy" /> <ScalarProperty Name="LastUpdatedBy" ColumnName="LastUpdatedBy" /> <ScalarProperty Name="AmtPaid" ColumnName="AmtPaid" /> <ScalarProperty Name="DatePaid" ColumnName="DatePaid" /> <ScalarProperty Name="P_I" ColumnName="P&amp;I" /> <ScalarProperty Name="Penalties" ColumnName="Penalties" /> <ScalarProperty Name="Interest" ColumnName="Interest" /> <ScalarProperty Name="CollectionFees" ColumnName="CollectionFees" /> <ScalarProperty Name="OtherFees" ColumnName="OtherFees" /> <ScalarProperty Name="Comments" ColumnName="Comments" /> <ScalarProperty Name="Balance" ColumnName="Balance" /> <ScalarProperty Name="dtLastUpdate" ColumnName="dtLastUpdate" /> <ScalarProperty Name="C45DayRedemptionAmt" ColumnName="45DayRedemptionAmt" /> <ScalarProperty Name="NextCriticalEvent" ColumnName="NextCriticalEvent" /> <ScalarProperty Name="NCEDate" ColumnName="NCEDate" /> </MappingFragment> </EntityTypeMapping> </EntitySetMapping> </EntityContainerMapping> </Mapping> </edmx:Mappings> </edmx:Runtime> <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) --> <edmx:Designer xmlns="http://schemas.microsoft.com/ado/2007/06/edmx"> <edmx:Connection> <DesignerInfoPropertySet> <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" /> </DesignerInfoPropertySet> </edmx:Connection> <edmx:Options> <DesignerInfoPropertySet> <DesignerProperty Name="ValidateOnBuild" Value="true" /> </DesignerInfoPropertySet> </edmx:Options> <!-- Diagram content (shape and connector positions) --> <edmx:Diagrams> <Diagram Name="Model1"> <EntityTypeShape EntityType="TAXTESTModel.TaxPmt" Width="1.5" PointX="0.75" PointY="0.75" Height="5.057109375" IsExpanded="true" /></Diagram></edmx:Diagrams> </edmx:Designer> </edmx:Edmx>

     




    Thursday, October 13, 2011 4:56 PM
  • Major strangeness going on.   I just don't see anything wrong with the code, the SSDL, or anything.

    Are there any update triggers on the TaxPmt table that could be doing something that causes a date conversion error?


    Tom Overton
    Thursday, October 13, 2011 5:02 PM
  • No Tom, no triggers or constraints. Very strange. I don't want to change the data type of my column to datetime either, because it will mess up my primary key.
    Thursday, October 13, 2011 5:07 PM
  •  

    Seeming that this is a test database or so I think does any of the year in that column AsOfDate is earlier then 1753?


    Fernando (MCSD)

    If a post answers your question, please click "Mark As Answer" on that post and "Mark as Helpful".
    Thursday, October 13, 2011 5:23 PM
  • No, I only have two records in the table at the moment, both of which I entered by hand. They both have values of '2011-10-07' (the other parts of the PK make them unique). The values I'm trying to insert are just DateTime.Today, which is showing

    DateTime.Today = {10/13/2011 12:00:00 AM}
    Thursday, October 13, 2011 5:34 PM
  • Brad,

    I really would like to see the sql that is getting sent when you attempt to do the update.  Can you run SQL Profiler on your database and capture that?


    Tom Overton
    • Marked as answer by BradInDallas Thursday, October 13, 2011 6:37 PM
    Thursday, October 13, 2011 5:49 PM
  • exec sp_executesql N'insert [dbo].[TaxPmt]([PayeeCode], [TaxID], [TaxYear], [RType], [AsOfDate], [dtCreate], [CreatedBy], [LastUpdatedBy], [AmtPaid], [DatePaid], [P&I], [Penalties], [Interest], [CollectionFees], [OtherFees], [Comments], [Balance], [dtLastUpdate], [45DayRedemptionAmt], [NextCriticalEvent], [NCEDate])
    values (@0, @1, @2, @3, @4, @5, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null)
    ',N'@0 decimal(10,0),@1 varchar(17),@2 int,@3 nchar(3),@4 datetime2(7),@5 datetime2(7)',@0=4811300000,@1='00000133786000000',@2=2010,@3=N'1/1',@4='2011-10-13 00:00:00',@5='0001-01-01 00:00:00'
    Thursday, October 13, 2011 6:28 PM
  • Ah ha! It wasn't the AsOfDate after all. It was the Create Date in parameter @5! I wasn't populating it and because it's set to nullable = false, it was pre-populating it with '0001-01-01 00:00:00', which was out of range. I added a line of code to set the Create date to today, and voila, it worked!

    It's an unexpected behavior for EF to populate a non-nullable datetime column automatically if no value is provided. Bug?

    Thank you thank you, Tom and Fernando. I wouldn't have thought about running SQL Profiler.

    Fixed!



    Thursday, October 13, 2011 6:36 PM
  • I just don't understand why that SQL wouldn't work.  Can you try to execute that from sql server management studio?  Does it work?

    Doing a simple test, there is no reason a datetime2(7) variable can't be inserted into a date column:

    declare @TaxPmt as table
    (
    AsOfDate date
    )
    
    declare @AsOfDate datetime2(7)
    
    set @AsOfDate = GETDATE()
    
    insert @TaxPmt
    (AsOfDate)
    select @AsOfDate
    

     


    Tom Overton
    Thursday, October 13, 2011 6:49 PM
  • Actually, it's not the AsOfDate that's the problem, it's the dtCreate.  It's defaulting it to '0001-01-01 00:00:00' and this will cause an error:

    declare @TaxPmt as table
    (
    dtCreate datetime
    )
    
    declare @dtCreate datetime2(7)
    
    set @dtCreate = '0001-01-01 00:00:00'
    
    insert @TaxPmt
    (dtCreate)
    select @dtCreate
    

    Try to populate a value for the dtCreate property on your entity and I think it will work.


    Tom Overton
    • Marked as answer by BradInDallas Thursday, October 13, 2011 6:57 PM
    Thursday, October 13, 2011 6:55 PM
  • Yes that was the problem, Tom. THanks for your help!

    Brad

    Thursday, October 13, 2011 6:58 PM
  • Okay i see you already were ahead of me and figured it out while i was doing my experiments! 

    But yeah, EF doesn't handle that very well.  But at least we know how to work around it now.

    Thanks Brad for the good screenshots and things to help us debug this and Fernando for the good suggestions.  Hopefully this will save some other developers some patches of hair in the future!

     

    Also, to the moderators, please move this thread to the ADO.NET Entity Framework and LINQ to Entities forum.  Thanks!

     


    Tom Overton
    • Edited by Tom_Overton Friday, October 14, 2011 12:44 AM update for moderators
    Thursday, October 13, 2011 6:59 PM