conversion of a varchar to a smalldatetime results in an out-of-range value
Solution 1
Try adding single quotes to your insert statement.
strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
Solution 2
Two possible answers:
- You need to encapsulate your string in an apostrophe:
strSql = "insert into table2 (transactiondate) values ('" & transactiondate & "')"
- You're using a date before 1/1/1900 or after 6/6/2079 (the limits of
smalldatetime
)
Solution 3
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
![Admin](/assets/logo_square_200-5d0d61d6853298bd2a4fe063103715b4daf2819fc21225efa21dfb93e61952ea.png)
Admin
Updated on July 29, 2022Comments
-
Admin almost 2 years
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.
-
jellomonkey almost 15 years+1 for just answering the question, but he still needs to fix the SQL injection problem.
-
Eric almost 15 years@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. -
Andrew over 3 yearsHumans create such finite solutions - everything we make has a time limitation.