CONVERSION FAILED WHEN CONVERTING DATA/OR time from character string VBA to SQL Server

Question

I'm trying to upload an Excel file into a SQL Server table, but I get this error:

CONVERSION FAILED WHEN CONVERTING DATA/OR time from character string VBA TO SQL SERVER

My source table has format date and my destination table have format date to.

What should I do to make this code work

I'm trying using CDATE() but it doesn't work.

Private Sub EventData_Click()

Set conn = New ADODB.Connection
cs = "DRIVER=SQL SERVER;"
cs = cs & "DATABASE=MAGANG;"
cs = cs & "SERVER=FELIX"

conn.Open cs, "", ""

sqlcmd = "CREATE TABLE Event_Data(ID Varchar(255) PRIMARY KEY, EVENT Varchar(255),DATE DATE);"

On Error GoTo errormastertable
conn.Execute sqlcmd
conn.Close
Set conn = Nothing

errormastertable:

    Dim l_row As Long
   
    Dim s_ID As String
    Dim s_EVENT As String
    Dim s_DATE As Date
 
Set conn = New ADODB.Connection
cs = "DRIVER=SQL SERVER;"
cs = cs & "DATABASE=MAGANG;"
cs = cs & "SERVER=FELIX"

    With Workbooks("Event_Data").Sheets("4-Event_Data")

conn.Open cs, "", ""
        l_row = last_row_with_data(1, ActiveSheet)
   
    For i = 2 To l_row
   
        s_ID = .Cells(i, 1)
        s_EVENT = .Cells(i, 2)
        s_DATE = .Cells(i, 3)
                               
        sqlcmd = "insert into dbo.Event_Data (ID,EVENT,DATE) values ('" & s_ID & "', '" & s_EVENT & "', '" & s_DATE & "')"
        conn.Execute sqlcmd
         Next
          conn.Close
        Set conn = Nothing

    End With
   
    Workbooks("Event_Data").Close savechanges:=False
    End If

End Sub

[VBA, ADO.Connection and query parameters](https://stackoverflow.com/a/10353908/2029983) is a good place to start — Thom A, Jul 01 '20 at 09:26
Please check the link Larnu provided, it contains a pretty good example. If you get stuck, don't hesitate to ask a new question. — HoneyBadger, Jul 01 '20 at 09:39

Dy.Lee · Accepted Answer · 2020-07-03T14:57:17.883

0

Numbers are generally without symbols
Characters have single quotes before and after
The datetime format is prefixed with # before and after the data.

sqlcmd = "insert into dbo.Event_Data (ID,EVENT,DATE) values ('" & s_ID & "', '" & s_EVENT & "', '" & s_DATE & "')"

to (In case of access file)

sqlcmd = "insert into dbo.Event_Data (ID,EVENT,DATE) values ('" & s_ID & "', '" & s_EVENT & "', #" & s_DATE & "#)"

But (in case of MSSQL)

  sqlcmd = "insert into dbo.Event_Data (ID,EVENT,DATE) values ('" & s_ID & "', '" & s_EVENT & "', cast('" & s_DATE & "' as datetime) )"

edited Jul 03 '20 at 14:57

answered Jul 01 '20 at 13:35

Dy.Lee

7,527
1
12
14

thanks bro but im but my problem already solved with this code ```_date = Year(s_DATE) & "/" & PadLeft(Month(s_DATE), 2, "0") & "/" & PadLeft(Day(s_DATE), 2, "0")``` – Lix Felix Jul 03 '20 at 09:35
@LixFelix, My answer was a bit confusing. mssql needs to convert datetime data to another format. Corrected the answer. – Dy.Lee Jul 03 '20 at 14:59

CONVERSION FAILED WHEN CONVERTING DATA/OR time from character string VBA to SQL Server

1 Answers1