Search code examples
sqlvbscriptdatetime-conversion

VB script with sql insert. Conversion of a varchar data type to DateTime

The current info needs to be handled:

Tid:            13.12.2014 01:48:48

what the script contains handling this is:

if isArray (arrString) then
  if uBound (arrString) > 0 then
    Select Case lcase (arrString(0))
      Case "tid": tid = trim (arrString (1)) & ":" & trim (arrString (2)) & ":" & trim (arrString (3))

When inserting with this code:

sql = "INSERT INTO LoginLogg VALUES ('" & tiden & "', '" & brukernavn & "', '" & maskinnavn & "', '" & operativsystem & "', '" & servicepack & "', '" & minne & "', '" & produsent & "', '" & modell & "', '" & bios & " / "  & bios2 & "', '"& serienummer & "', '"& printere & "', '"& ipadresse & "', '"& imagedato & "', '" & opplosninger & "')"

This into SQL it returns:

the conversion of a varchar data type to a datetime data type resulted in an out-of-range value errorcode: 80040e07

I cant find the reason why it wont work now. The database was moved from a 2003 to a 2008 R2.

If this would have been powershell i would have been able to solve this but VB isnt my strongest suit.

Updated the title hoping it is more suiting, along with the sql insertion code.

Solution
    1. Fix the discrepancy between tid and tiden
    2. See if feeding a m/d/y string - trim (arrString (2)) & "/" & trim (arrString (1)) & "/" & trim (arrString (3)) 'works'
    3. If (2) fails or you don't want a hack, try to concat a date literal "#m/d/y#" into your SQL statement
    4. If (3) fails or you want to do it correctly, use an ADODB.Command/prepared statement with a date parameter - obtained by DateSerial() on CInt(arrString(...))