2001-01-01 00: 00: 00.000 inserted into the database instead of 2000-12-31 23:59:59

Question

I have a method that adds a row to the database (sql server 2005). Something is wrong with this, because when I have a line with UpdateDate 2000-12-31 23:59:59 , it inserts 2001-01-01 00:00:00.000 . Is it possible? An environmental culture is Polish, if important. This is magic for me: /

 private void AddInvestmentStatus(InvestmentData.StatusyInwestycjiRow investmentStatusesRow) { SqlCommand cmd = new SqlCommand("AddInvestmentStatus"); cmd.CommandType = CommandType.StoredProcedure; SqlParameter param1 = new SqlParameter("@InvestmentId", SqlDbType.BigInt); param1.Value = investmentStatusesRow.InvestmentId; cmd.Parameters.Add(param1); cmd.Parameters.AddWithValue("@enumInvestmentStatusID", investmentStatusesRow.EnumInvestmentStatusID); cmd.Parameters.AddWithValue("@modifiedBy", "System"); cmd.Parameters.AddWithValue("@UpdateDate", investmentStatusesRow.UpdateDate); cmd.Parameters.AddWithValue("@ModifiedOn", investmentStatusesRow.ModifiedOn); cmd.Parameters.AddWithValue("@dataVersion", investmentStatusesRow.DataVersion); cmd.Connection = new SqlConnection(MyProgram.Properties.Settings.Default.ConnectionString); if (cmd.Connection.State != ConnectionState.Open) cmd.Connection.Open(); try { cmd.ExecuteNonQuery(); } catch (Exception e) { throw; } } 

}

 create PROCEDURE [dbo].[AddInvestmentStatus] @inwestmentID bigint, @enumInvestmentStatusId bigint, @updateDate datetime, @dataVersion int, @modifiedBy nvarchar(50), @modifiedOn datetime AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @investmentStatusesID bigint INSERT INTO StatusyInwestycji(InwestycjaID) VALUES (@inwestmentID) SELECT @investmentStatusesID = SCOPE_IDENTITY(); INSERT INTO StatusyInwestycjiData(InvestmentStatusId, EnumStatusInwestycjiID, UpdateDate, DataVersion, ModifiedBy, ModifiedOn) VALUES (@investmentStatusesID, @enumInvestmentStatusId, @updateDate, @dataVersion, @modifiedBy, @modifiedOn) END 

EDIT:

my date:

 {2000-12-31 22:59:59} Date: {2000-12-31 00:00:00} Day: 31 DayOfWeek: Sunday DayOfYear: 366 Hour: 22 Kind: Utc Millisecond: 999 Minute: 59 Month: 12 Second: 59 Ticks: 631139003999990000 TimeOfDay: {22:59:59.9990000} Year: 2000 

Answer 1

Are you sure you enter 23: 59: 59.000000 or are you entering 23: 59: 59.9999999?

DateTime SQL data type has an accuracy of 3.33 ms (it will be rounded to 0 ms, 3 ms, 7 ms), i.e. your 23: 59: 59.9999 will be rounded to 00: 00: 00.000000 the next day.

Answer 2

I don’t know if this is the case in your case, but this is a known problem when the datetime values, where the millisecond part is 0.998 or 0.999, are rounded to the next whole second value when pasted into the database, which in the worst case scenario can lead to that the date and time value will be carried forward to next year.

Answer 3

If the table has a smalldatetime column, this is possible since it has an accuracy of 1 minute

see here

 select CONVERT(smalldatetime,'2000-12-31 23:59:59') 

does not happen with datetime

 select CONVERT(datetime,'2000-12-31 23:59:59') 

Send DDL tables, make sure you also check triggers that might call me smalldatetime