Insert dd/mm/yyyy hh:mm:ss.000 in datetime in SQL Server

Question

I have a timestamp in Excel in the format d/m/yyy hh:mm:ss.000 and I send it to a datetime datatype in SQL Server. But when I transfer the data it has transferred correctly but without the milliseconds every data has .000 on the end. In Excel I have the date with the correct milliseconds. Does someone have a solution?

I think the main question is... how are you transferring the data from Excel to SQL Server in the first place? — FAB, May 03 '19 at 09:34
@DarXyde I call this module to send the data to the sql server. — Mitchel, May 03 '19 at 09:38
Convert them to the following format to send them `YYYY-MM-DD hh:mm:ss.000` (`Format$()`) you might also be interested in reading [Why is SQL Server losing a millisecond?](https://stackoverflow.com/questions/715432/why-is-sql-server-losing-a-millisecond) — Pᴇʜ, May 03 '19 at 10:05

score 0 · Answer 1 · edited May 03 '19 at 09:59

Sub Sectie1Invert()

Dim Cn As ADODB.Connection
Dim ServerName As String
Dim DatabaseName As String
Dim TableName As String
Dim UserID As String
Dim Password As String
Dim rs As ADODB.Recordset
Dim rs1 As ADODB.Recordset
Dim RowCounter As Long
Dim NoOfFields As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim ColCounter As Integer
Dim trigger As Boolean

Set rs = New ADODB.Recordset

ServerName = "NLDONL0113" ' Enter your server name here
DatabaseName = "Stroomwaarden" ' Enter your  database name here
TableName1I = "Sectie1invert" ' Enter your Table name here
 UserID = "" ' Enter your user ID here
Password = "" ' Enter your password here
NoOfFields = 1 ' Enter number of fields to update (eg. columns in your worksheet)
StartRow = 3 ' Enter row in sheet to start reading  records
EndRow = 349 ' Enter row of last record in sheet

Dim shtSheetToWork As Worksheet
Set shtSheetToWork = ActiveWorkbook.Worksheets("Sheet1")
 '********

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & ServerName & ";Database=" & DatabaseName & _
";Uid=" & UserID & ";Pwd=" & Password & ";"
rs.Open TableName1I, Cn, adOpenKeyset, adLockOptimistic

For RowCounter = StartRow To EndRow
   rs.AddNew
    'On Error Resume Next

     rs(0) = shtSheetToWork.Cells(RowCounter, 13)
    rs(3) = shtSheetToWork.Cells(RowCounter, 14)

    Debug.Print RowCounter
Next RowCounter

    rs.UpdateBatch
    rs.Close
    Set rs = Nothing
    Cn.Close
    Set Cn = Nothing
End Sub

If this is not the answer to your question please edit your original question and put the code there (and then delete this answer). Don't use the answer button to add code, only use it if you want to provide an answer to the original question. You might want to take the [Tour] or read [ask]. — Pᴇʜ, May 03 '19 at 10:01

score 0 · Answer 2 · answered May 03 '19 at 09:55

0

You can get Text of Cell and format before insert to db:

Dim strValue as String
strValue=shtSheetToWork.Cells(RowCounter, 13).Text

format again strValue before insert to db rs(0), if it is datetime type.

answered May 03 '19 at 09:55

D T

3,522
7
45
89

You can cut String or set format before get text : ex: shtSheetToWork.Cells(RowCounter, 13).NumberFormat = "yyyy-MM-dd HH:mm:ss.000" rs(0)=shtSheetToWork.Cells(RowCounter, 13).Text – D T May 03 '19 at 11:03
I get type mismatch error on: rs(0)=shtSheetToWork.Cells(RowCounter, 13).Text – Mitchel May 03 '19 at 11:13
I can't test, You write and execute a SQL instead of UpdateBatch – D T May 03 '19 at 11:52
Have anyone a solution? I have search but I cant find a solution. – Mitchel May 08 '19 at 06:47
Can you copy a datetime value of your db? – D T May 08 '19 at 08:12
This is a value copied from the db: 2019-05-08 09:00:43.000 – Mitchel May 08 '19 at 08:15
You try a sql ex: Cn.Execute("Update Sectie1invert set [Col]='2019-05-08 09:00:43.123' where ID=[key]"). Is it oK? – D T May 08 '19 at 08:21
yes this is working, do I need to use the Cn.Execute function ? – Mitchel May 08 '19 at 08:30
yes. you must format datetime in excel before get text. – D T May 08 '19 at 08:32
In my excel sheet I have already set the format of the cell to Custom and :yyyy-mm-dd hh:mm:ss.000 – Mitchel May 08 '19 at 08:49
Good, You can get Text and use Execute function to update or insert. – D T May 08 '19 at 08:52
I don't understand it for 100%. I can get the cell value in text to set .text on the end. But I don't know how I can send it with Cn,Execute function. Now I do it with rs.AddNew en then I send the value in the column with rs(0) and rs(3). – Mitchel May 08 '19 at 09:03
above you had execute ok: Cn.Execute("Update Sectie1invert set [Col]='2019-05-08 09:00:43.123' where ID=[key]") – D T May 08 '19 at 09:05
you only set column and value to sql and execute it. – D T May 08 '19 at 09:06
it works when I use a date like: Cn.Execute ("Update Sectie1invert set [date]='2019-09-22 12:22:10.928' where ID=[ID]" But the whole column have the same date,. – Mitchel May 08 '19 at 09:22
If you add new, try this sql: sql="Insert Sectie1invert (Col1,Col2) values('" & shtSheetToWork.Cells(RowCounter, 13).Text & "','" & shtSheetToWork.Cells(RowCounter, 13).Text & "')" – D T May 08 '19 at 09:26
I am getting a syntax error: Cn.Execute ("Insert Sectie1invert (Col1,Col2) values('" & shtSheetToWork.Cells(RowCounter, 4).Text & "','" & shtSheetToWork.Cells(RowCounter, 5).Text &"')" – Mitchel May 08 '19 at 09:43
What is your sql? You must change [Col1,Col2] to column name in your db. – D T May 08 '19 at 09:46
It is working, is it possible to send the range D2:D110 in one execute? Will that be faster to insert into the sql server? – Mitchel May 08 '19 at 11:22
You can try, add ";" and new line to last a sql. – D T May 08 '19 at 11:28
When I insert this: Cn.Execute ("Insert Sectie1invert (Sectie1invert) values('" & shtSheetToWork.Range("D2;D110").Value & "','" & shtSheetToWork.Range("E2;E110").Value & "')") I get this error: Method 'Range' of object'_ worksheet' failed – Mitchel May 08 '19 at 12:00
No.you must connect all query and execute a time: ex: sql="Insert Sectie1invert (Col1,Col2) values('" & shtSheetToWork.Cells(RowCounter, 13).Text & "','" & shtSheetToWork.Cells(RowCounter, 13).Text & "');" & vbNewLine & next sql – D T May 09 '19 at 01:53

Insert dd/mm/yyyy hh:mm:ss.000 in datetime in SQL Server

2 Answers2