Uploading data from Excel to SQL formatting date

Question

I am using VBA to upload data from and Excel file into SQL Server. The date column in the CSV as I get is in the "mm/dd/yy format". How do I format the date to be in the SQL Server format "YYYY-MM-DD hh:mm:ss:000" before I pass it to the query to insert the record?

I declare the variable

Dim InvoiceDate As Date

I get the value from the workbook

InvoiceDate = row.Cells(5).Value

I insert into the table

INSERT INTO table(InvoiceDate) VALUES (" & InvoiceDate & ")

I end up with this:

1900-01-01 00:00:00.000

Why write your own script in VBA if there is an Import and Export Wizard that is fully documented and supports Microsoft Excel? — Sander
– Sander, Commented Nov 10, 2020 at 18:58
@BigBen is correct - Format$(InvoiceDate,"YYYY-MM-DD hh:mm:ss:000") will do it. — Scott Holtzman
– Scott Holtzman, Commented Nov 10, 2020 at 19:04
SQL Injection alert - you should not concatenate together your SQL statements - use parametrized queries instead to avoid SQL injection - check out Little Bobby Tables — marc_s
– marc_s, Commented Nov 10, 2020 at 19:34
@ScottHoltzman is correct but I suspect the InvoiceDate in the code is assigned the value of an empty cell! — Jalal
– Jalal, Commented Nov 10, 2020 at 19:48

Michael Tuma · Accepted Answer · 2020-11-11 18:58:39Z

1

So I added my own variable after getting the value from the Excel worksheet and before uploading to SQL: sDate = Format(InvoiceDate, "YYYY-MM-DD hh:mm:ss:000")

answered Nov 11, 2020 at 18:58

Michael Tuma

777 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Uploading data from Excel to SQL formatting date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related