0

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!

jarlh
  • 42,561
  • 8
  • 45
  • 63
SSss0011
  • 3
  • 1
  • Show us the querytxt contents. – jarlh Jan 18 '23 at 14:56
  • ADO? DAO? What is the content of `Sh.Cells(cl.Row, Range("Table [Date Planned]").Column)` (you should write this in an intermediate variable anyhow). If ADO: Have you considered to use ADODB.Parameters? – FunThomas Jan 18 '23 at 15:03

1 Answers1

0

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.

marritza
  • 22
  • 5
  • a) A date in Excel is stored as Double, not as Integer (the Integer part gives the date, the fraction the time). b) We don't know the content of the cell of OPs data, but even assuming it is a date: By using `&` there is an implicit conversion into a String, so the database will never see the number, only the string. You can get away by tinkering with routines like `Format` to craft a string that the database can then convert back to a date, but the much cleaner way is to use ADODB.Parameter (if you are using ADO). – FunThomas Jan 18 '23 at 15:24