Solved

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

Posted on 2011-09-19
10
670 Views
Last Modified: 2012-05-12
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
Comment
Question by:doramail05
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 15

Expert Comment

by:Haris Djulic
ID: 36558715
can you debug to see in what format the dtArrivedTime.Date is displayed i.e. how the date is passed to server?
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36558766
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
 
LVL 1

Author Comment

by:doramail05
ID: 36558872
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!

 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36558884
You NEED to make both dates in the same formatted string otherwise it won't work correctly.
0
 
LVL 8

Expert Comment

by:gena17
ID: 36558888
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
 
LVL 1

Author Comment

by:doramail05
ID: 36558896
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
 
LVL 1

Author Comment

by:doramail05
ID: 36558899
it couldnt i mean
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 36558913
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 36558920
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
 
LVL 3

Accepted Solution

by:
Srm74 earned 500 total points
ID: 36558931
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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question