between operator

Hi,

I'm having problems using the between operator in a query statement.

Select *
from <mytable>
where date between @.date1 and @.date2

The date values with a hour specified, aren't returned. What is the approach you would recommend here?

Thx

EDIT: by playing with this problem I've figured out I can append the hour to the date like this: <date> between @.fromDate + '00:00:00' and @.toDate + '23:59:59'

this seem to work, but I'm not sure if this is correct

Select *
from <mytable>
where date >= @.date1 and date < dateadd(d,1,@.date2)

or

Select *
from <mytable>
where date >= @.date1 and date < @.date2 +1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

i had a similar problem which i did through the application side:

string weekdate =txtFrom.Text;

string daydate = System.Convert.ToDateTime(txtTo.Text).Add(System.TimeSpan.FromDays(1)).ToString();

and later...

TechnicianViewInboxDataAdapter.SelectCommand.CommandText = tempselecttechnician + " where status = 1 and TechnicianID = " + Session["UserID"].ToString() + "and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '" + weekdate + "' and '" + daydate + "' and DepartmentID = " + Session["DepartmentID"].ToString();

this works as well.

the code ends up like this:

SELECT FirstName, LastName, Email, CallNumber, DepartmentName, UserName, Status, TechnicianID, CallLoggedDT, DepartmentID, CallOpenedDT, CallActionedDT, CallClosedDT FROM dbo.TechnicianInboxView where status = 1 and TechnicianID = 2and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and DepartmentID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' or TechnicianID = 1 and status = 1 and CallLoggedDT Between '2006/05/30' and '2006/06/07 12:00:00 AM' and DepartmentID = 2

|||

You can also use the datediff function and the statement would look like this.

Select * from <mytable> where

datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

@.Date can be varchar too and it can be in the format of "mm/dd/yy" also. Need not have the time factor.

|||

where datediff(dd,@.date1,<Datefield in the table>)>=0 and datediff(dd,@.date2,<Datefield in the table>)<=0

Would not recommend doing so in the terms of performace. You will never get index seek over the index for datefield column: it will always index scan at the best.

between dates

in sql2000
"select * from tbl where tbl.date between date1 and date2" - results with
nothing
"select * from tbl where tbl.date >= date1 " - gives result
"select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
with nothing
how to select between 2 dates?
thanksHow about providing us with real specs, e.g. are the columns really
datetime/smalldatetime? What data is in them? What values are you using
for date1 and date2? What format are they in?
Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
only get vague answers.
"Sam" <focus10@.zahav.net.il> wrote in message
news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||column date is datetime type
the values are simple:
"select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
gives NO RESULT
"select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
"select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives NO
RESULT
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
> How about providing us with real specs, e.g. are the columns really
> datetime/smalldatetime? What data is in them? What values are you using
> for date1 and date2? What format are they in?
> Please see http://www.aspfaq.com/5006 ... otherwise vague questions will
> only get vague answers.
>
> "Sam" <focus10@.zahav.net.il> wrote in message
> news:ey3GNgirFHA.248@.TK2MSFTNGP14.phx.gbl...
>|||See point number 3 in the first link.
Should I use BETWEEN in my database queries?
http://www.aspfaq.com/show.asp?id=2280
The ultimate guide to the datetime datatypes
http://www.karaszi.com/SQLServer/info_datetime.asp
AMB
"Sam" wrote:

> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
NO
> RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>
>|||If u r using date1 and date2 as parameters, in ur case date2 must be null.
Even if one of the dates is null, BETWEEN or simple comparison will fail as
happening in ur cases.
pls chk and let me know in case of any dbts
Rakesh
"Sam" wrote:

> in sql2000
> "select * from tbl where tbl.date between date1 and date2" - results with
> nothing
> "select * from tbl where tbl.date >= date1 " - gives result
> "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results
> with nothing
> how to select between 2 dates?
> thanks
>
>|||(a) you need to delimit datetime values with '
(b) never use ambiguous and silly formats like m/d/y or d/m/y. Is
01/05/2005 January 5th or May 1st? Who knows?
Try:
WHERE date >= '20020101' AND date <= '20050105'
"Sam" <focus10@.zahav.net.il> wrote in message
news:ehA02sirFHA.2592@.TK2MSFTNGP09.phx.gbl...
> column date is datetime type
> the values are simple:
> "select * from tbl where tbl.date between 01/01/2002 and 01/05/2005" -
> gives NO RESULT
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
> "select * from tbl where tbl.date >=01/01/2002 and <=01/05/2005" - gives
> NO RESULT
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OQO7bjirFHA.3440@.TK2MSFTNGP10.phx.gbl...
>|||
> "select * from tbl where tbl.date >= 01/01/2002" - gives GOO DRESULT
What are the date values for these results? Are they all later than
"01/05/2005"?
Perayu|||i found the solution:
@.date1 nvarchar(12), @.date2 nvarchar(12),
'BETWEEN CONVERT(DATETIME, ' + char(39) + @.date1 + char(39) + ', 102) AND
CONVERT(DATETIME, '+ char(39) + @.date2 + char(39) +', 102)'
"Rakesh" <Rakesh@.discussions.microsoft.com> wrote in message
news:790C5553-6B88-45F8-A1D6-4F32CF001010@.microsoft.com...
> If u r using date1 and date2 as parameters, in ur case date2 must be null.
> Even if one of the dates is null, BETWEEN or simple comparison will fail
> as
> happening in ur cases.
> pls chk and let me know in case of any dbts
> Rakesh
> "Sam" wrote:
>