How do I insert Excel date format (mm/dd/yyyy) into SQL Server format (yyyy-mm-dd)?

Question

There is already pre-inputed data in the Excel table in dd/mm/yyyy date format. How can I bulk insert the Excel values in a .csv file into the SQL Server table with yyyy-mm-dd date format?

question from:https://stackoverflow.com/questions/66066545/how-do-i-insert-excel-date-format-mm-dd-yyyy-into-sql-server-format-yyyy-mm-d

Answer 1

Well, CSV files don't have formatting, and 'bulk insert the Excel values in a .csv file' doesn't make a whole lot of sense. If you want to push data from Excel to SQL Server, you have many options to do so. If the data set is samll, you can use an Excel Macro to do the work.

Sub InsertInto()

'Declare some variables
Dim cnn As adodb.Connection
Dim cmd As adodb.Command
Dim strSQL As String

'Create a new Connection object
Set cnn = New adodb.Connection

'Set the connection string
cnn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Northwind;Data Source=Server_Name"


'Create a new Command object
Set cmd = New adodb.Command

'Open the Connection to the database
cnn.Open

'Associate the command with the connection
cmd.ActiveConnection = cnn

'Tell the Command we are giving it a bit of SQL to run, not a stored procedure
cmd.CommandType = adCmdText

'Create the SQL
strSQL = "UPDATE TBL SET JOIN_DT = '2013-01-22' WHERE EMPID = 2"

'Pass the SQL to the Command object
cmd.CommandText = strSQL


'Execute the bit of SQL to update the database
cmd.Execute

'Close the connection again
cnn.Close

'Remove the objects
Set cmd = Nothing
Set cnn = Nothing

End Sub

If you data really is in a CSV file, you can bulk insert the CSV data into SQL Server.

BEGIN TRANSACTION
BEGIN TRY
BULK INSERT  OurTable 
FROM 'c:OurTable.txt' 
WITH (CODEPAGE = 'RAW', DATAFILETYPE = 'char', FIELDTERMINATOR = '', 
   ROWS_PER_BATCH = 10000, TABLOCK)
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH 

Of course you can also go to SQL Server and import the CSV file from there.

https://www.sqlshack.com/importing-and-working-with-csv-files-in-sql-server/