wrong date convert from Excel to MS SQL (DD.MM.YYYY becomes YYYY-DD-MM)

Viewed 41

please help me with this thing. I have table in excel with dates:

enter image description here

enter image description here

Then by VBA I import it to ms sql server into a table, but the result is wrong: in Excel date format DD.MM.YYYY, but after import date becomes to YYYY-DD-MM? instead of YYYY-MM-DD

VBA code: Sub Button1_Click()
     
    Dim conn As New ADODB.Connection
    Dim iRowNo As Integer
    Dim smdm_id As String, sdate_start As String, sdate_end As String
  
    With Sheets("Ââîä")
            
        'Open a connection to SQL Server
        conn.Open "Provider=SQLOLEDB;Server=actuar11;Database=marketing_sbx;Trusted_Connection=yes;Integrated Security=SSPI;"
            
        'Î÷èñòèì òàáëèöó
        conn.Execute "truncate table ol_del_export_from_excel_macro_mdm"
        
        'conn.Execute "Update X Set X.date_start = TRY_CONVERT(Date, X.date_start, 103) FROM ol_del_export_from_excel_macro_mdm X"
        'conn.Execute "Update X Set X.date_end = TRY_CONVERT(Date, X.date_end, 103) FROM ol_del_export_from_excel_macro_mdm X"
            
        'Skip the header row
        iRowNo = 2
            
        'Loop until empty cell in CustomerId
        Do Until .Cells(iRowNo, 1) = ""
            smdm_id = .Cells(iRowNo, 1)
            sdate_start = .Cells(iRowNo, 2)
            sdate_end = .Cells(iRowNo, 3)
                
            'Generate and execute sql statement to import the excel rows to SQL Server table

            conn.Execute "insert into dbo.ol_del_export_from_excel_macro_mdm (mdm_id, date_start, date_end) values ('" & smdm_id & "', '" & sdate_start & "', '" & sdate_end & "')"
            iRowNo = iRowNo + 1
        Loop
                                
        MsgBox "Ñòðàõîâàòåëè è äàòû èìïîðòèðîâàíû."
        

        conn.Close
        Set conn = Nothing

             
    End With

End Sub
1 Answers

Here is that help me: Original code:

conn.Execute "insert into dbo.ol_del_export_from_excel_macro_mdm (mdm_id, date_start, date_end) values ('" & smdm_id & "', '" & sdate_start & "', '" & sdate_end & "')"

Changed code:

conn.Execute "insert into dbo.ol_del_export_from_excel_macro_mdm (mdm_id, date_start, date_end) values ('" & smdm_id & "', '" & Format(sdate_start, "YYYY-MM-DD") & "', '" & Format(sdate_end, "YYYY-MM-DD") & "')"
Related