SELECT last date from database column entries !

jeshra279

Hi All, I have a SQL database column, name "Closing Date" with datatype as Datetime. For example, the entries are like this: 1-Nov-2009 21-Dec-2009 10-Jan-2010 21-Mar-2010 11-Apr-2010 Now What I want is to select a date which is less than my requested date from a text box. e.g. If I want to select a date which is less that 31-Mar-2010, then I should get 21-Mar-2010. or, If I want to select a date which is less that 9-Jan-2010, then I should get 21-Dec-2009. Presently I am using following in VB.net. cmd = New SqlCommand("SELECT [Closing Date] FROM client_details WHERE [Closing Date] <= textbox.text", con) But I think I need to use something else. Hope My query is clear. Please suggest me how to go ahead? Regards R.S.

O Phil

Try

cmd = New SqlCommand("SELECT [Closing Date] FROM client_details WHERE [Closing Date] <= @selectedDate", con);

Dim param As New SqlParameter("selectedDate", DateTime.Parse(textbox.Text, CultureInfo.InvariantCulture));
cmd.Parameters.Add(param);

Luc Pattyn

Hi, try

"SELECT MAX([Closing Date]) FROM client_details WHERE [Closing Date] <= '"+textbox.text+"'"

(assuming C# is what you need). With some comments: 1. it is much easier when database field names don't contain special characters and spaces. 2. such SQL statement is vulnerable to SQL injection: users could enter parts of an SQL command, which your code then executes. Solution: verify the textbox content, or better yet, use SQLparameter. 3. the user must enter a string representing a date, according to the format your database is expecting. It is better to have the user enter data according to the system's regional settings, then to convert it to what the database needs. Again SQLParameter is the better approach: use DateTime.Parse(textbox.text) (or TryParse), then set the SQLParameter. :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

Prolific encyclopedia fixture proof-reader browser patron addict?
We all depend on the beast below.

Mycroft Holmes

Here is another alternative - no better, no worse just different.

"SELECT TOP 1 [Closing Date]) FROM client_details
WHERE [Closing Date] <= '"+textbox.text+"'
order by [Closing Date] DESC"

As Luc suggested use parameterized queries - this is IMPORTANT. There are usually a number of ways to sole a problem, try both and look at the execution plans to decide the better solution. Then throw an index on the close date field and see the difference!

Never underestimate the power of human stupidity RAH