Results 1 to 9 of 9

Thread: [RESOLVED] C# to Pull SQL Results based on day

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2010
    Posts
    272

    Resolved [RESOLVED] C# to Pull SQL Results based on day

    I know how to connect to SQL Server & pull the query results into Excel. What I do not know how to do in C# is to only Select the results where the saledate is today. SaleDate in my SQL table is a datetime field. I tried using this as my C# SQL Query
    Code:
    DateTime dt;
    dt = DateTime.Now;
    string queryString = "SELECT * FROM totalSales WHERE employee = @employee AND saleDate =  dt.DayOfWeek.ToString() ORDER BY convert(datetime,saledate,101) DESC";
    But that is throwing an error Can one of you more advanced coders point out my error
    Last edited by Jo15765; Jun 25th, 2013 at 10:22 AM. Reason: typo in SQL here not in my source coding

  2. #2
    Hyperactive Member
    Join Date
    Nov 2008
    Location
    PA
    Posts
    365

    Re: C# to Pull SQL Results based on day

    Which part is erroring, the saleDate = dt.DayOfWeek.ToString() or the convert(datetime,saledate,101)?

    My guess is the conversion. Why convert it to a datetime if it's already a datetime field? Can't you just use ORDER BY saleDate DESC?

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: C# to Pull SQL Results based on day

    You can't put C# code into a SQL query. That query is just a String so, in that context, 'dt' means nothing. That SQL query is sent to the database and it knows nothing about a local variable in your C# method. You have to get the actual date value in your C# code and pass that to the query, which you would do using a parameter. To learn how to do that, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.

    Also, why are you ordering the data on a column that, by your WHERE clause, can only contain one value?

    Finally, please tell that your 'saleDate' column is not a text data type. If it contains dates/times then it should be a data type specifically for dates/times. If you have control of the schema then you should change that immediately.

  4. #4
    Hyperactive Member
    Join Date
    Nov 2008
    Location
    PA
    Posts
    365

    Re: C# to Pull SQL Results based on day

    You can't put C# code into a SQL query.
    Of course, I didn't even notice it was part of the query and not a parameter or concatenated.

    At the very least, concatenate the query string with the dt.DayOfWeek.ToString(). Really follow JMC's advice and use parameters.

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2010
    Posts
    272

    Re: C# to Pull SQL Results based on day

    Quote Originally Posted by jmcilhinney View Post
    You can't put C# code into a SQL query. That query is just a String so, in that context, 'dt' means nothing. That SQL query is sent to the database and it knows nothing about a local variable in your C# method. You have to get the actual date value in your C# code and pass that to the query, which you would do using a parameter. To learn how to do that, follow the Blog link in my signature and check out my post on Parameters In ADO.NET.
    Ah, I didn't think of passing a parameter to it, I was just trying to use it all in the string.


    Quote Originally Posted by jmcilhinney View Post
    Also, why are you ordering the data on a column that, by your WHERE clause, can only contain one value?
    I have it set-up that way as when I just used Order By Desc it wouldn't put the years together. For example, it would group all of Jan 2012 together, then Jan 2013. Was very peculiar result sets returned and this way it actually grouped date wise descending.

    Quote Originally Posted by jmcilhinney View Post
    Finally, please tell that your 'saleDate' column is not a text data type. If it contains dates/times then it should be a data type specifically for dates/times. If you have control of the schema then you should change that immediately.
    Yes, this column is a datetime field. Thank you for checking

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2010
    Posts
    272

    Re: C# to Pull SQL Results based on day

    Thanks to the guidance offered here, I now have my code executing as needed. Below is my final result (and you guys may say there are more efficient ways
    Code:
    DateTime dt;
    dt = DateTime.Now;
    string queryString = "SELECT * FROM totalSales WHERE employee = @employee AND saleDate =  @SD ORDER BY convert(datetime,saledate,101) DESC";
    SqlConnection conn - new SqlConnection("Sql Connection Information Is here");
    SqlCommand cmd = new SqlCommand(queryString, conn);
    if (eID == "m1483")
    {
    	cmd.Parameters.AddWithValue("@employee", "Michael");
    	cmd.Parameters.AddWithValue("@SD", dt.ToString());
    }
    //Additional if's to follow

  7. #7
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: [RESOLVED] C# to Pull SQL Results based on day

    Noooooooooo! You said that your 'saleDate' column is type 'datetime' so why are you comparing it to a text value? 'dt' is a DateTime so why are you calling ToString on it? Pass in the DateTime to compare to the datetime.

    Also, your ordering still makes no sense at all. Firstly, your converting a datetime column to type datetime. If it's already a datetime then what's the conversion for? Secondly, you're filtering the data by a specific 'saleDate' value so how can ordering that data by that column be beneficial when every row contains the same value?

    Finally, do you really want to get records where the 'saleDate' is equal to the current date and time? Would you even have any records with exactly that value? I suspect that what you really want is records with today's date irrespective of time. Is that correct? If so, does the database contain times as well or just dates?

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2010
    Posts
    272

    Re: [RESOLVED] C# to Pull SQL Results based on day

    Quote Originally Posted by jmcilhinney View Post
    Noooooooooo! You said that your 'saleDate' column is type 'datetime' so why are you comparing it to a text value? 'dt' is a DateTime so why are you calling ToString on it? Pass in the DateTime to compare to the datetime.
    You lost me there m8.

    Quote Originally Posted by jmcilhinney View Post
    Also, your ordering still makes no sense at all. Firstly, your converting a datetime column to type datetime. If it's already a datetime then what's the conversion for? Secondly, you're filtering the data by a specific 'saleDate' value so how can ordering that data by that column be beneficial when every row contains the same value?
    Converting/ordering in this instance made it all fall sequential order. I don't remember what the exact issue was, but it was something to do with databases that had records from 2012 and 2013 they wouldn't order descending properly.

    Quote Originally Posted by jmcilhinney View Post
    Finally, do you really want to get records where the 'saleDate' is equal to the current date and time? Would you even have any records with exactly that value? I suspect that what you really want is records with today's date irrespective of time. Is that correct? If so, does the database contain times as well or just dates?
    No, I actually just saw that was posing an issue (trying to match date and time). So I used this
    Code:
    string DateOnly = DateTime.Now.ToString("MM/dd/yyyy");
    And passed that as the parameter to only display records with today's date.

  9. #9
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,302

    Re: [RESOLVED] C# to Pull SQL Results based on day

    I give up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width