I'm trying to insert using vba into a table on sql server, it gives an error:

"Conversion failed when converting date and/or time from character string."

  querytxt = "update TABLE set dateplan=N'" & Sh.Cells(cl.Row, Range("Table [Date Planned]").Column) & "'" & _

    " where ID='" & Sh.Cells(cl.Row, Range("Table[ID]").Column) & "'"

I understand the problem, but I don’t fully understand what function / conversion I should use and where to make it work in code. thanks in advance!

CodePudding user response：

it seems that you try to insert a string into sql field with a datatype like e.g. date or datetime. so you could try add a convert or cast to your SQL part of the query. E.g. in case of TSQL: https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver16

You should be aware that in Excel dates are stored as integers and only displayed as dates in the cells https://support.microsoft.com/en-us/office/date-systems-in-excel-e7fe7167-48a9-4b96-bb53-5612a800b487 so it might be the case that your SQL command tries to insert these integers, in this case, you would need to handle that convert Excel Date Serial Number to Regular Date

One hint: print your SQL command at runtime using e.g. VBA Debug.Print to see what the actual SQL command comes out of your VBA.