CODEWITHSUNDEEP
c#sqlsql-server-2008
Kǒng Qiū
Kǒng Qiū

Reputation: 31

How to Search in the database according to Month and Year chosen from Combo Boxes?

I have an application where I register some information into the database where I have a Column (DateTime). In this column I insert the date from a DateTimePicker.

Now I want to make a search button which searches according to the date chosen from Comboboxes... but in this Comboboxes I left only one option, to select MONTH and YEAR... how can I make an SELECT query that selects all information according to the Month and Year chosen from the ComboBoxes?

Upvotes: 0

Views: 2181

Answers (4)

es137custom
es137custom

Reputation: 21

Make sure you store the combo values into Integer variables in your C# before putting them in the SQL query. As others have alluded, SQL Injection attacks are possible if you allow the direct user input to be placed inside a SQL String.

Assuming you have 2 int variables "intMonth", "intYear", and the field name of "dateFieldname", the following SQL should work.

SELECT  *
FROM    [table]
WHERE   datepart[mm,dateFieldname] = intMonth
    AND datepart[yyy,dateFieldname] = intYear

Upvotes: 1

Satinder singh
Satinder singh

Reputation: 10198

Something like this

Select * from tablename where datepart(mm,datecolumnname)=@cmboxMonthvalue nd datepart(yyyy,datecolumnname)=@ComboboxYearvalue

Upvotes: 0

alzaimar
alzaimar

Reputation: 4622

All solutions which use either the datepart or month and year functions make it impossible for the server to optimize the query by using an index.

The only valid solution in terms of performance is @Sparkys second approach using the between clause. He also described the problems arising with the time fraction. That is why I would prefer using a plain date column (instead of datetime). Then you can write

select * 
from YourTable 
where DateColumn between <FirstDayOfSelectedMonthAndYear> 
                     and <LastDayOfSelectedMonthAndYear>

because when using a datetime column and the range of

between '20121201' and '20121231 23:59:59'

Everything after the last second of the year and midnight still gets discarded. Although it is very unlikely it is technically not correct.

@Nicarus suggests widening the ending time to 23:59:59,997'. This seems to work but is 'ugly' (but, wtf, it works!)

Upvotes: 1

Sparky
Sparky

Reputation: 15095

select * from <table>
where month(searchDate)=Month_from_box and Year(searchDate)=Year_from_box

However, depending on the size of your data, this might not be the fastest approach. If you data is in the 100's or even 1000 rows, this approach might be OK...

Another approach would be to build starting and ending dates in C# from your input and then perform a range search

select * from <table>
where searchDate between Start_date_from_C# and End_Date_from_C#

If you go that approach, be sure to consider the time portion, make it 0 in first date and 23:59:59 in the End Date

If you have an index on the date field, the second approach will be faster...

Upvotes: 1

Related Questions