Solved

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

Posted on 2011-09-19
10
656 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
 
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

707 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now