Convert datetime to smalldatetime in sql insert query

Question

I have an INSERT query like this

INSERT INTO tna_temp_attendance VALUES('" + strEmpCode + "', CONVERT(SMALLDATETIME, '" + dtDateTime.ToString() + "'), 0, " + _inOutMode.ToString() + ", null, 0, 'CHO-', '" + strMachine + "')

When I execute this query there is this error occurred "the conversion of a varchar data type to a datetime data type resulted in an out-of-range value"

Help Plz

My database has this table "tna_temp_attendance" and its columns are

• Emp_id char(6)
• Wdate smalldatetime
• Is_read bit
• Machine_Status bit
• Process_Status bit
• Is_Error bit
• Loc_no char(4)
• Ip varchar(16)

Edit

I passed the date like this also

CONVERT(SMALLDATETIME, '" + Convert.ToDateTime(dtDateTime.ToString("yyyy-MM-dd hh:mm:ss")) + "')

But it also not working

Answer 1

I finally done it. I declared a string.

• string dt = DateTime.Now.ToString();

Then convert it to DateTime

DateTime ndt = Convert.ToDateTime(dt);

Then I used

CONVERT(SMALLDATETIME, CONVERT(DATETIME, '" + ndt + "'))

And It Worked. Thanks guys

Answer 2

Try to use ISO format to insert date into SQL server as below:

dtDateTime.ToString("yyyyMMdd")