Milliseconds missing when insert date time to sql

Question

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;
}

I hope this will not be your first post, Please care to keep proper formatting while posting — sujith karivelil, Jun 29 '16 at 06:01
sidenote: sql server's `datetime` data type best accuracy is 3 milliseconds. If you need milliseconds accuracy you should either use `Time` datatype or `DateTime2`. — Zohar Peled, Jun 29 '16 at 06:07
@Zohar, DATETIME still returns milliseconds, so DATETIME2 would not solve the problem since it is his compiler (C#) that is causing the errors. By definition, VAR in C# is an implicitly typed local variable. [var (C# Reference)](https://msdn.microsoft.com/en-us/library/bb383973.aspx) — clifton_h, Jun 29 '16 at 06:11
3 milliseconds enough for me but it always 000 in my case that's my problem — Manikandan, Jun 29 '16 at 06:13
Oh, really? From a simple google search, `DATETIME` has no implicit format, so that may be the culprit here.combined with your compiler's default rendering of `DATETIME`. *user2380844* [LINK](http://stackoverflow.com/questions/23927093/how-to-convert-var-into-date-time-using-c) has a similar question where the answer was to explicitly translate `DATETIME` into `string` (Which, incidentally, you do in `SQL` too). — clifton_h, Jun 29 '16 at 06:19
@clifton_h there is no problem while it convert to var but in my db it have the date time datatype so i need to send this into datetime in user define table but it miss the milliseconds :( — Manikandan, Jun 29 '16 at 06:23
Ok, cart before the horse. Where does the `DATETIME` values come from? Your DB or the C# program? What happens when you translate it into string directly first, as the link describes? If your still getting a TRUNCATION of values, then check upstring in your code. — clifton_h, Jun 29 '16 at 06:35
When i convert the var date = (DateTime.Parse(datetime.ToString("yyyy-MM-dd HH:mm:ss.fff"))); it return correct milliseconds — Manikandan, Jun 29 '16 at 06:37
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/115921/discussion-between-clifton-h-and-manikandan). — clifton_h, Jun 29 '16 at 06:41
@clifton_h: I know, I was only pointing out that it's accuracy is limited to 3 milliseconds. that's why it was a side note. I didn't even try to to answer the question. — Zohar Peled, Jun 29 '16 at 06:50
I've tried to reproduce your problem but couldn't. I did get milliseconds every time. My c# code was exactly like yours except I've used `DateTime.Now` as the value to insert into the dataRow. Perhaps the `data.ModifiedDate` is already with milliseconds set to 0? — Zohar Peled, Jun 29 '16 at 08:11
@Manikandan please *simplify* the code and include the code that actually generates the date. This code already does some really weird things, like using `Type.GetType` instead of just `typeof(DateTime)`. It also uses `ConvertToDataTable` but you post a different method. The stored procedure code doesn't compile either - you can't write `VALUES(@Test ModifiedDate)`. Perhaps you have yet-another-typo that removes milliseconds — Panagiotis Kanavos, Jun 29 '16 at 08:24
@PanagiotisKanavos VALUES( ModifiedDate) only i used mistakenly i posted VALUES(@Test ModifiedDate), typeof(Datetime) also wont work — Manikandan, Jun 29 '16 at 08:40
@ZoharPeled i get millisecond always 000 that the my problem, in by table the entry look like 2016-06-28 12:53:20.000 — Manikandan, Jun 29 '16 at 08:42
I understand the problem, but can't reproduce it. BTW, your insert statement should look like this: `INSERT INTO ZZZ_TEMP_TABLE (ModifiedDate) SELECT ModifiedDate FROM @Test` — Zohar Peled, Jun 29 '16 at 08:44

score 1 · Accepted Answer · answered Jun 29 '16 at 20:13

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.

Milliseconds missing when insert date time to sql

1 Answers1