How can i do this query?? [modified]

foryou

HI!! I have a table (sqlserver2005) ID-------date1--------date2 1--------1/12/2008-----30/5/2009 2--------1/12/2009-----30/6/2010 ... I typed a date in a textbox. I need to select ID as that date will be between date1 and date2. How can i do that thanks. exemple whene date=30/2/2009 id is 1. Thanks!

modified on Tuesday, January 6, 2009 4:57 AM

Wendelius · modified on Tuesday, January 6, 2009 4:57 AM

You can use between operator like:

SELECT ...
FROM ...
WHERE DateFromTextBox BETWEEN date1 AND date2

The need to optimize rises from a bad design.My articles[^]

foryou

HI!! I did this :

String rqtr2 = "select ID as [p1] from Table where ('" + TextBox2.Text + "' between date1 and date2)";

and

TextBox2.Text = Convert.ToString(DateTime.Now) ;

but it is not right . Help me please! thanks

Wendelius

foryou wrote:

but it is not right

What do you mean by that? Could you post an example row and what you entered in the textbox. That would clarify your problem. The forst guess would be that when you convert the date to a string, it isn't in a format that the db understands. Little off-topic: it's a bad habit to use literals in the sql statement. Use parameters instead.

The need to optimize rises from a bad design.My articles[^]

modified on Wednesday, January 7, 2009 7:27 AM

foryou · modified on Wednesday, January 7, 2009 7:27 AM

Hi! Merci Mika Wendelius sorry I dont speak English well. what I am in the textbox is the system date (detetime) example : 07/01/2009 11:59:16 Little off-topic: it's a bad habit to use literals in the sql statement. Use parameters instead. I do not know how to use parameters in this case. Thanks!!

Wendelius

foryou wrote:

sorry I dont speak English well

No problem at all

foryou wrote:

what I am in the textbox is the system date (detetime) example : 07/01/2009 11:59:16

ok, you may encounter a conversion problem. If you're using literals (which I do not encourage at all) you would have a query like

select ...
from ...
where CONVERT(datetime, '07/01/2009 11:59:16', 101) between date1 and date2

You can test this for example like this:

select *
from sysobjects
where CONVERT(datetime, '01/07/2009 11:59:16',101) -- now
between GETDATE()-1 -- yesterday
and GETDATE()+1 -- tomorrow

Notice that the format is mm/dd/yyyy. if you want to use dd/mm/yyyy, you should use British/French format:

CONVERT(datetime, '07/01/2009 11:59:16', 103)

However you won't have these problems at all if you use parameters. Then your query would look like

select ...
from ...
where @SomeDateParameter between date1 and date2

There are good examples in BOL: SqlParameter[^].

foryou wrote:

Merci Mika Wendelius

Ne rien. (I hope it was correct :))

The need to optimize rises from a bad design.My articles[^]

foryou · CONVERT(datetime, '07/01/2009 11:59:16', 103)

Hi!! Thank you very much Mika Wendelius! it works. Mika Wendelius wrote Ne rien. (I hope it was correct ) they say "De rien". :)

Wendelius

foryou wrote:

Thank you very much

You're welcome :)

foryou wrote:

they say "De rien".

I had a feeling it was wrong. Thanks for correcting! It's been over 20 years I studied french and never used it after that so I guess I have forgotten about everything :)

The need to optimize rises from a bad design.My articles[^]