Solved

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

Posted on 2011-09-19
10
659 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
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Count with a subquery showing details 10 44
List<PaisEntity> - show some 1 24
sql how to count case when 4 18
Job - date manual 1 22
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

832 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