VBA日期时间格式并导入到sql

Question

我对VBA到SQL server 2008 R2的日期时间格式有点问题，因为导入的日期时间只是这样的日期：

2020-08-10 00:00:00.000

我想要：

2020-08-10 09:15:30.000

这是我的VBA代码：

Sub Data_transform()
Sheets("Poz_data").Range("A:P").NumberFormat = "General"
Sheets("Poz_data").Range("A:A").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Sheets("Poz_data").Range("H:H").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Sheets("Poz_data").Range("J:J").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Sheets("Poz_data").Range("K:K").NumberFormat = "dd/mm/yyyy hh:mm:ss"
Sheets("Poz_data").Range("O:O").NumberFormat = "0"
End Sub
Sub Data_import()

Dim colum_a As Long
Dim colum_h As Long
Dim colum_j As Long
Dim colum_k As Long
Dim colum_s As Long

colum_a = Sheets(PD).Cells(k, 1).Value
colum_b = Sheets(PD).Cells(k, 2).Text
colum_c = Sheets(PD).Cells(k, 3).Text
colum_d = Sheets(PD).Cells(k, 4).Text
colum_e = Sheets(PD).Cells(k, 5).Text
colum_f = Sheets(PD).Cells(k, 6).Text
colum_g = Sheets(PD).Cells(k, 7).Text
colum_h = Sheets(PD).Cells(k, 8).Value
colum_i = Sheets(PD).Cells(k, 9).Text
colum_j = Sheets(PD).Cells(k, 10).Value
colum_k = Sheets(PD).Cells(k, 11).Value
colum_l = Sheets(PD).Cells(k, 12).Text
colum_m = Sheets(PD).Cells(k, 13).Text
colum_n = Sheets(PD).Cells(k, 14).Text
colum_o = Sheets(PD).Cells(k, 15).Text
colum_p = Sheets(PD).Cells(k, 16).Text
colum_q = Sheets(PD).Cells(k, 17).Text
colum_r = Sheets(PD).Cells(k, 18).Text
colum_s = Sheets(PD).Cells(k, 19).Value

Set rs = e.Execute("INSERT INTO Pozadavky_Source_New (DATUM_ZADANI, ZADAVATEL, ID_POZADAVKU, RESITELSKY_TYM, KATEGORIE, NAZEV_POZADAVKU, STAV, DATUM_ODESLANI_KE_ZPRACOVANI, URGENTNI, TERMIN, DATUM_POSLEDNI_ZMENY, AUTOR_POSLEDNI_ZMENY, ZPETNY_KONTAKT_NA_KLIENTA, ZPETNY_KONTAKT_NA_KLIENTA_TYP, ZPETNY_KONTAKT_NA_KLIENTA_UDAJ, HISTORIE, ZDROJ, ID_ZDROJ, ID_POZADAVKU_SHORT) VALUES (" & colum_a & ", '" & colum_b & "', '" & colum_c & "', '" & colum_d & "', '" & colum_e & "', '" & colum_f & "', '" & colum_g & "', " & colum_h & ", '" & colum_i & "', " & colum_j & ", " & colum_k & ", '" & colum_l & "', '" & colum_m & "', '" & colum_n & "', '" & colum_o & "', '" & colum_p & "', '" & colum_q & "', '" & colum_r & "', " & colum_s & ")")

End Sub

谢谢你的回复，这对我来说非常重要。有什么建议吗？

Answer 1

例如，如果column_k是一个日期，我会像这样键入

Format(colum_k,"dd-mm-yyyy hh:mm:ss")

Set rs = e.Execute("INSERT INTO Pozadavky_Source_New (DATUM_ZADANI,ZADAVATEL, ID_POZADAVKU, RESITELSKY_TYM, KATEGORIE, NAZEV_POZADAVKU, STAV, DATUM_ODESLANI_KE_ZPRACOVANI, URGENTNI, TERMIN, DATUM_POSLEDNI_ZMENY, AUTOR_POSLEDNI_ZMENY, ZPETNY_KONTAKT_NA_KLIENTA, ZPETNY_KONTAKT_NA_KLIENTA_TYP, ZPETNY_KONTAKT_NA_KLIENTA_UDAJ, HISTORIE, ZDROJ, ID_ZDROJ, ID_POZADAVKU_SHORT) VALUES (" & colum_a & ", '" & colum_b & "', '" & colum_c & "', '" & colum_d & "', '" & colum_e & "', '" & colum_f & "', '" & colum_g & "', " & colum_h & ", '" & colum_i & "', " & colum_j & ", " & Format(colum_k,"dd-mm-yyyy hh:mm:ss") & ", '" & colum_l & "', '" & colum_m & "', '" & colum_n & "', '" & colum_o & "', '" & colum_p & "', '" & colum_q & "', '" & colum_r & "', " & colum_s & ")")