CODEWITHSUNDEEP
c#sql-serverdatetimestored-procedurestable-valued-parameters
Manikandan
Manikandan

Reputation: 894

Milliseconds missing when insert date time to sql

I m using user define table parameter for bulk insert, i create the user define table and column name is ModifiedDate in datatype for datetime

when i pass the value into sql it will insert fine but it missed milliseconds value then how can i install this

My user define table

CREATE TYPE [dbo].[Test] AS TABLE(
[ModifiedDate] [datetime] NOT NULL,
)

My Sp 

ALTER PROCEDURE [dbo].[CP_UpdateData]
-- Add the parameters for the stored procedure here
 @Test Test Readonly,

  INSERT into Test(ModifiedDate)
       Values(ModifiedDate);

but here my datetime value is missing millisecond, could you please help any suggestion for resolve this issues

in by c# code 

using (var cmd = new SqlCommand())
{
    cmd.CommandText = "CP_UpdateData";
    cmd.Connection = con;
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("Test", SqlDbType.Structured).Value = ConvertToDataTable(list);                       
    con.Open();
    var dataReader = await cmd.ExecuteReaderAsync();
}

public DataTable ConvertToDataTableCampaingList(List<Test> list)
{
    var dataTable = new DataTable();

    if (list != null && list.Count > 0)
    {
     dataTable.Columns.Add("ModifiedDate", Type.GetType("System.DateTime"));
     foreach (var data in list)
        {
        var dataRow = dataTable.NewRow();
        dataRow["ModifiedDate"] = data.ModifiedDate;
         dataTable.Rows.Add(dataRow);
        }
    }
    return dataTable;
}

Upvotes: 1

Views: 3941

Answers (1)

clifton_h
clifton_h

Reputation: 1298

The answer being in the CHAT ROOM discussion, I will post it here: The problem is implicit conversions and how each compiler treats the data. DATETIME by default has no defined FORMAT, so SQL implicitly converts the data.

So the issue is when your storing it into the table as the Default formatting is the problem CREATE TABLE #Example2 (TIMES DATETIME NOT NULL) INSERT INTO #Example2 (TIMES) VALUES (CONVERT(DATETIME, GETDATE(), 9)) , (CAST(GETDATE() AS VARCHAR(20) ) ) Notice how the default in a simple string actually drops milliseconds since its format is wrong

Notice the solution was explicitly defining the format:

  • Manikandan wrote:

    When i convert the var date = (DateTime.Parse(datetime.ToString("yyyy-MM-dd HH:mm:ss.fff"))); it return correct milliseconds

Converting the DATETIME to string makes it portable and in a data type that will not TRUNCATE the data. However, use a proper CONVERT(data type, expression, style) if you ensure accuracy.

  • Manikandan wrote:

    DECLARE @TEMP_Result TABLE ( ModifiedDate DATETIME ) 

    DECLARE @TEMp TABLE 
( 
ModifiedDate  varchar(50) 
) 

declare @timestring varchar(50) 

set @timestring = '2016-06-28 12:53:20.850' 

Insert into @TEMp(ModifiedDate) 
values(@timestring) 

Insert into @TEMP_Result(ModifiedDate) 
select Convert(datetime, ModifiedDate) from @TEMp 


select * from @TEMP_Result

MORAL: BEWARE OF IMPLICIT CONVERSIONS

  • Implicit conversion are guesses and determined by the compiler. They are not dependable as this case shows.

  • CAST is not an explicit conversion, and may return the wrong format. Use CONVERT in SQL to avoid implicit conversions.

  • Storing DATETIME in a string makes it portable, avoids TRUNCATION of data, and is easily converted to the correct format in SQL.

Upvotes: 1

Related Questions