• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 694
  • Last Modified:

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

trying to match the date only in Convert(arrivedtime) with datetime.date in sql server, c#

but couldnt getting any record,
string strSQL = "select sa1.arrivedtime as arrivedtime, sa1. from traineemodule tm " +
                "JOIN StudentAttendanceC sa1 WHERE CONVERT(VARCHAR(10), arrivedtime,111) = '" + dtArrivedTime.Date + "'";

eg.
---

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';

Open in new window

0
doramail05
Asked:
doramail05
1 Solution
 
Haris DjulicCommented:
can you debug to see in what format the dtArrivedTime.Date is displayed i.e. how the date is passed to server?
0
 
AndyAinscowCommented:
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
0
 
doramail05Author Commented:
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;
    }

Open in new window

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
AndyAinscowCommented:
You NEED to make both dates in the same formatted string otherwise it won't work correctly.
0
 
gena17Commented:
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") + "'";
0
 
doramail05Author Commented:
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.
0
 
doramail05Author Commented:
it couldnt i mean
0
 
AndyAinscowCommented:
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.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you may want to read this article to learn about how to handle date/time correctly in databases:
http://www.experts-exchange.com/A_1499.html
0
 
Srm74Commented:
This works for me :-)
I made a Store function, having a datetime as a parameter, feeding it with at Standard C# DateTime obj.
I can post that if you like...

A rewrite to your parameternames is something like..


string strSQL = "select sa1.arrivedtime as arrivedtime, sa1. from traineemodule tm " +
                "JOIN StudentAttendanceC sa1
WHERE
dateadd(month,(((Year(arrivedtime))-1900)*12)+Month(arrivedtime)-1,Day(arrivedtime)-1) = dtArrivedTime


0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now