SQL date insertion from Excel results in wrong date value

77 Views Asked by At

I have problem executing part of my SQL query from Excel. Note that the same query works from my Manager's PC, and both are on the same Regional settings in CP.

Here's code:

Do While Range("A" & CStr(Red)).Value <> ""

    Sql_Query = "UPDATE [DB-NAME] SET [Expiration Date] = '" & Format(Trim(Range("H" & CStr(Red)).Value), "yyyy-mm-dd") & _
       "' WHERE (No_ = '" & Trim(Range("A" & CStr(Red)).Value) & "') AND ([Pallet Place No_] = '" & Trim(Range("B" & CStr(Red)).Value) & "') AND " & _
       "([Item No_] = '" & Trim(Range("C" & CStr(Red)).Value) & "')"
    Set Rs = Conn.Execute(Sql_Query)
    'Rs.Close ' Closing query
    Set Rs = Nothing

    Red = Red + 1 'Switching to the next row after one is finished
Loop

When I execute this part, Mr. Code inserts code like this:

"UPDATE [DB-NAME] SET [Expiration Date] = '7627-09-30' WHERE (No_ = '267917') AND ([Pallet Place No_] = '11110102') AND ([Item No_] = '116000')"

Note: this is text that 'Add Watch' option returns.

Even though in that 'H' column date is like '2018-12-30'.

Cell format in Excel doesn't make change. Its not important if its General or Text or Date or Custom-date, problem remains. And this thing with Mr. Code is consistent.

1

There are 1 best solutions below

1
On

Is that query works at all from yours computer? Try update other field (it's better number type).