3

The code:

strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"

seems to be giving me the runtime error:

The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value

In the code, strSql is a String object and transactiondate is a Date object. In the SQL database however, transactiondate is a smalldatetime object.

I've tried changing the smalldatetime to a datetime (in the database) and I've tried transactiondate.toString() but with no success.

How can this be fixed?

Note: I know about the dangers of inline SQL. I am looking for a quick fix solution here and not a discussion about SQL injection.

4 Answers4

6

Try adding single quotes to your insert statement.

strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
Jose Basilio
  • 50,714
  • 13
  • 121
  • 117
  • 1
    +1 for just answering the question, but he still needs to fix the SQL injection problem. – jellomonkey Jul 10 '09 at 20:00
  • 4
    @jello: The OP posted a disclaimer about his awareness of SQL injection. This is a code snippet. You have no idea what cleansing `transactiondate` has already gone through. Every discussion about SQL does NOT need to include SQL injection. – Eric Jul 10 '09 at 20:02
3

Two possible answers:

  1. You need to encapsulate your string in an apostrophe:

strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"

  1. You're using a date before 1/1/1900 or after 6/6/2079 (the limits of smalldatetime)
Eric
  • 92,005
  • 12
  • 114
  • 115
1

Smalldatetime can only accept values for years > 1900 and < 2079 as well. I had a similar error to the one you posted where the solution was to filter out invalid years like such

where year(datecolumn) < 2079
jarofclay
  • 680
  • 4
  • 12
0

Use transactiondate.ToString("yyyy/MM/dd HH:mm:ss") or your prefered date format. If it remains, change 'sa' user default language, or whatever user you use, to your prefered language.

TlmaK0
  • 3,578
  • 2
  • 31
  • 51