Converting varchar to smalldatetime results in a value out of range

Question

Code:

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

seems to give me a 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. However, in a SQL database, transactiondate is a smalldatetime object.

I tried changing smalldatetime to datetime (in the database) and I tried transactiondate.toString (), but without success.

How can this be fixed?

Note. I am aware of the dangers of embedded SQL. I am looking for a quick solution to the solution here, and not a discussion of SQL injection.

Answer 1

Try adding single quotes to the insert statement.

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

Answer 2

Two possible answers:

• You need to encapsulate your string in the apostrophe:

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

2. 1/1/1900 6/6/2079 ( smalldatetime)

Answer 3

transactiondate.ToString( "yyyy/MM/dd HH: mm: ss" ) . , "" , , .

Answer 4

Smalldatetime > 1900 < 2079. , , ,

where year(datecolumn) < 2079