CODEWITHSUNDEEP
c#asp.netsqldatatable
BlahWoo
BlahWoo

Reputation: 365

Filter datatable between two dates

I have a column within my database called "Month". What i want to do is filter the table between two months. Example of this is March - June. I have code which works BUT only works alphabetically

string strquery = "select * from tbl_DR_data ";
string strq2 = "where";

if (DropDownList6.SelectedItem.Text != "All" && DropDownList8.SelectedItem.Text != "All") {
    string month1 = DropDownList6.SelectedItem.Text.ToString();
    string month2 = DropDownList8.SelectedItem.Text.ToString();
    strq2 = strq2 + "[Month] BETWEEN'" + month1 + "'AND'" + month2 + "'";
}

When DropDownList6 = March and DropDownList8 = June. Nothing appears in the gridview which im binding BUT if the swap them around so DDL6= June and DDL8 = March it works :S

Is there a work around so that i can have the months ordered in how the months are meant to be instead of being alphabetical

Upvotes: 0

Views: 1880

Answers (4)

Learning
Learning

Reputation: 20001

I am not sure what your DB design is or even what the Month Fields data-type is, I agree with other first & foremost that this is not a good design as i cant think of a scenario where i can search only based on month. This should be a complete date fields.

It can give us better idea if you give us very brief about this functionality like where you want to use it & purpose.

Please don't take me as a critic... more details about your project can help us better understand & recommend solution. May be i am wrong as i may have not understand your question.

You can use nice solution given but think for the road ahead..

Upvotes: 0

Kaf
Kaf

Reputation: 33809

This is the MSDN Syntax for BETWEEN

test_expression [ NOT ] BETWEEN begin_expression AND end_expression

Why no results when When DropDownList6 = March and DropDownList8 = June?

With above values, your query becomes like

[Month] BETWEEN 'March' AND 'June'

Unfortunately, when you are filtering using BETWEEN, begin_expression should be less than or equal to end_expression. In your case, since you have stored months (names) as string in the database, March comes after June in alphabetical order and so above condition returns false and no results.

This is a fiddle example same as yours with no results.

You can get the month from a date/datetime field using various methods. In sql server, you can use;

//Month NUMBER
month(datefield) as month_Number 
//Month NAME
datename(month,datefield) as month_Name

Just to give you worst side of your design. Just check the results of filtering by where month between 'april' and 'may'.

Your results include: August, December, February, January, July, June, March

Solution:

For the time since you don't have full date, I guess it would be better to update your database filed as below and change the datatype of [month] to int type;

update yourTable
set [month] = case [month] when 'January' then 1
                           when 'February' then 2
                           ...
                           when 'December' then 12 end

Now change the data type of [month] to int type and then pass the month number from the code as

[month] BETWEEN 3 and 6

Also, try to avoid key words such as Month for field names.

Upvotes: 0

watraplion
watraplion

Reputation: 287

try this code, Hope this what you expected

SET @FromMonth = 'March'
SET @ToMonth = 'June'
SELECT 
  * 
FROM 
  tbl_DR_data
WHERE
   DATEPART(mm,CAST([Month]+ ' 1900' AS DATETIME)) >= DATEPART(mm,CAST(@FromMonth+ ' 1900' AS DATETIME))
   AND DATEPART(mm,CAST([Month]+ ' 1900' AS DATETIME)) <= DATEPART(mm,CAST(@ToMonth+ ' 1900' AS DATETIME))

Upvotes: 0

StackTrace
StackTrace

Reputation: 9416

why are you really storing months as strings in the database?, if you follow that path, trust me you are even going to encounter much more serious problems down the road & they will be a hell to debug. You can always get the month part from any date or dateTime column value.

Upvotes: 1

Related Questions