Search code examples
c#sqldatetimesql-insertsmalldatetime

Convert datetime to smalldatetime in sql insert query

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

Solution

  • 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