Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    22

    Unanswered: Date comparisons using parameters --- forward slash is interpreted as divide :(

    I'll get straight to it, since I think this is also probably an "easy fix." This is the code that is run as part of a search, and the searchstring is a huge long string generated based on what the user chooses. This section comes as part of the WHERE. Anyway...

    Code:
            mysqlcommand.Parameters.AddWithValue("@StartYear", StartYearDDList.SelectedItem.Text);
            mysqlcommand.Parameters.AddWithValue("@StartMonth", StartMonthDDList.SelectedItem.Text);
            mysqlcommand.Parameters.AddWithValue("@EndYear", EndYearDDList.SelectedItem.Text);
            mysqlcommand.Parameters.AddWithValue("@EndMonth", EndMonthDDList.SelectedItem.Text);
    
            searchstring += " (Cases.Date >= @StartYear/@StartMonth AND Cases.Date <= @EndYear/@EndMonth)";
    ---

    Error: "Operand data type nvarchar is invalid for divide operator." Googling this exact error returns a whopping two results (literally). Putting in the numbers manually, i.e.

    Code:
    searchstring += " (Cases.Date >= '2004/07' AND Cases.Date <= '2010/09')";
    works fine, gives me the results I want. Doing it with parameters in this way, however, fails with the above error.

    Tried various things suggested around the net, such as double slashes, concatenating the string, various combinations of using single quotes with slashes as an attempt to escape the character, etc. etc., nothing has worked so far.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You submit strings to SQL Server that are implicitly converted to dates. '2004/07' is no date SQL Server (nor I) recognise. Are you trying to return all rows where [Date] is between July 2004 and September 2010?
    If so then:
    Code:
    searchstring += " (Cases.Date >= '20040701' AND Cases.Date <= '20100930')";
    ref:
    Tibor Karaszi's SQL Server pages
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I've read your code a little more closely. It would be easier to write it like this (then you don't need to know how many days in each month).
    Code:
    searchstring += " (Cases.Date >= '20040701' AND Cases.Date < '20101001')";
    The reason it fails too is different to what I wrote: the error message does describe the exact problem - you have defined your parameters as NVARCHAR and by using the "/" divide operator you have attempted to divide them. Really you wanted to concatenate them, however that would have failed too unless you concatenated them into a value that can be implicitly converted into a date.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2010
    Posts
    22
    Well, the column is not of datetime, but nvarchar. The values in this column are of the format "2004/09/22". This application is for creating logs of events. So if we engage in some activity on September 30th, 2010, we may not actually get around to entering it into the database until say, today. In which case, we use drop down lists on the user interface side of things to choose the year, month, and day of the event (in this case, 2010/09/30). This is just concatenated into a string like 2010 + / + 09 + / + 30. I found just using nvarchar to be simpler because when I tried using datetime initially, it automatically slapped this long time down the second as well, automatically set to like 00:00:00. I guess that's no problem, it just wasn't pleasing to see when looking at query results.

    The date comparison works fine if you do it without parameters. Like if I type:

    Code:
    searchstring += " (Cases.Date >= '2004/07' AND Cases.Date <= '2010/09')";
    as in my code, it properly gives me all records of July 2004 through September 2010. I tested this in like a billion different ways, and the comparisons are all accurate, it gives me exactly what I want. It just won't work when you do it with parameters and put a slash there, because it thinks you're dividing it.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You need to concatenate them then; you need the / to be part of a string and not the divide operator.
    BTW - data types are the simplest constraint in an RDBMS. Selecting the wrong one (especially for dates) is very common and fraught with complications. If it's a date, store a date. If it's a number, store a number. If it's binary, store binary.
    Working with Date and/or Time values in SQL Server: Don't Format, Don't Convert -- just use DATETIME
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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