Match date only with datetime.date in sql server, c#

can you debug to see in what format the dtArrivedTime.Date is displayed i.e. how the date is passed to server?

Try converting both dates to the same format, something like the following:

JOIN StudentAttendanceC sa1 WHERE CONVERT(VARCHAR(10), arrivedtime,111) = '" + CONVERT(VARCHAR(10), dtArrivedTime.Date, 111) + "'";

Note this is air code, you will probably have to modify the quote marks to make it fit

guess somehow that might not be working, coz its coming from a parameter the dtArrivedTime.Date

public static DataTable getStudentAttendance_ByArrivedTime(DateTime dtArrivedTime)
    {
        string strConnString = connstring.getIDashString();

        using (SqlConnection sqlconn = new SqlConnection(strConnString))
        {
            DataTable dt = new DataTable();
            string strSQL = "select sa1.arrivedtime as arrivedtime, sa1. from traineemodule tm " +
                "JOIN StudentAttendanceC sa1 WHERE CONVERT(VARCHAR(10), arrivedtime,111) = '" + dtArrivedTime.Date + "'";
            SqlDataAdapter adpt = new SqlDataAdapter(strSQL, sqlconn);
            adpt.Fill(dt);

            if (dt.Rows.Count > 0)
            {
                return dt;


            }

        }

        DataTable dt01 = new DataTable();
        return dt01;
    }

Select allOpen in new window

You NEED to make both dates in the same formatted string otherwise it won't work correctly.

I guess SQL CONVERT(VARCHAR(10), arrivedtime,111) format is "yyyy/MM/dd", and not "MM/dd/yyyy".

That said, try formatting C# to the same format:

string strSQL = "select sa1.arrivedtime as arrivedtime, sa1. from traineemodule tm " +
                "JOIN StudentAttendanceC sa1 WHERE CONVERT(VARCHAR(10), arrivedtime,111) = '" + dtArrivedTime.Date.ToString("yyyy/MM/dd") + "'";

if like that,

string strSQL = "select sa1.arrivedtime as arrivedtime, sa1. from traineemodule tm " +
                "JOIN StudentAttendanceC sa1 WHERE CONVERT(VARCHAR(10), arrivedtime,111) = '" + dtArrivedTime.Date.ToString("yyyy/MM/dd") + "'";

somehow i tried the sample data like 01-09-2011,  (or 09-01-2011 or 09-01-2011 or 01-09-2011)

select sa1.arrivedtime as arrivedtime from traineemodule tm
                JOIN StudentAttendanceC sa1 ON sa1.id = tm.said WHERE CONVERT(VARCHAR(10), arrivedtime,111) = '09/01/2011';

it could retrieve any record, but there is record in db.

it couldnt i mean

What does the following give:

SELECT CONVERT(VARCHAR(10), sa1.arrivedtime,111) as xxx;

That should show you what the result of the CONVERT is.

you may want to read this article to learn about how to handle date/time correctly in databases:
https://www.experts-exchange.com/A_1499.html