Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2011-09-19
10
Medium Priority
?
687 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 45

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 45

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 45

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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

610 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