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 Query - HELP!

Hi,

I have an SQl query that will list the results if it lies between two dates, for example;

SELECT * FROM TABLE WHERE { fn Now() } BETWEEN Date1 AND Date2

This returns all results where Date1 and Date2 fall between the Current Date

What i am looking for is a way to replace the { fn Now() } with a date of my choice.

For example;

SELECT * FROM TABLE WHERE '10/10/2006' BETWEEN Date1 AND Date2

However this does not work.

Has anyone any ideas why this may be ??

Thanks in advance

Andrew Vint

try to use an sql server variable and assign it with the date u want and then put it in ur select statement

|||

Hi,

I am afraid that does not work either.

Thanks

|||

You could use a variable in the SQL query. If it didnt work for you please post the code you have.

|||Are you sure you want to put a constant on the left while 2 fields on the right? Then you have to make sure Date2 is greater than Date1, otherwise the expression will return false no matter what's the constant. Another thing you need to pay attention to is the DATEFORMAT option of current user (which can be checked using DBCC USEROPTIONS), you can useSET DATEFORMAT command to change the current date format, or always use the ODBC date format yyyy-mm-dd.|||

Hi,

Thanks for the response.

I have tried using the yyyy-mm-dd format without sucess i am afraid.

Any more ideas anyone ?

Andrew

|||

I was able to run your query without a problem on SQL 2005. The query below also runs.

SELECT * FROM myTable
WHERE cast('9/1/2006' as datetime) BETWEEN Date1 AND Date2

What are the data types for your Date1 and Date2 fields? I assume they are either datetime or smalldatetime, right? Could the problem be that they are bigint fields which store ticks instead of the actual datetime? Or are they char/varchar fields that are storing the date as a string?

Can you describe the problem you have? Do you get an error message? Do you get zero results or results that seemingly should not be returned?

|||That's really strange. It would help if we can repro your issue. Can you post some sample data from the table you're trying to query, as well as the result you expect to get, and the actual result the query returns?|||

Hi All,

Thank you for your assitance in this but after hours of staring at the same lines of query strings it has become clear that it was staring me in the face all along.

I had a date convert on the date2, because of this it was not working. As soon as i removed the convert it worked as i had expected it too.

Now before you all deservedly laugh at me please look at what i had to work with below;

strSQLHoliday = "SELECT TBL_Holidays.AgentRef, TBL_Holidays.EmpVacatID, " _
& "TBL_Agents.LastName + ', ' + TBL_Agents.FirstName AS FullName, " _
& "TBL_Teams.TeamName, CONVERT(varchar(12), TBL_Holidays.ActivityDate, 106) " _
& "AS ActDate, CONVERT(varchar(12), (DATEADD(d, CASE DATEPART(dw, ActivityDate) " _
& "WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate) + " _
& "(DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN " _
& "2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) - 2 + " _
& "CAST(TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs / 5) AS Int)) % 5 " _
& "+ (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN " _
& "2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) - 2 + " _
& "CAST(TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs / 5) AS Int)) " _
& "/ 5 * 7) - (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, ActivityDate) " _
& "WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, TBL_Holidays.ActivityDate)) " _
& "- 2), 106) AS ReturnDate, TBL_Agents.ContractHrs / 5 AS DailyHrs, " _
& "TBL_Holidays.TimeTaken, TBL_Holidays.TimeTaken / (TBL_Agents.ContractHrs " _
& "/ 5) AS DaysTaken, TBL_Holidays.Status, TBL_Holidays.TheNote FROM " _
& "TBL_Holidays INNER JOIN TBL_Agents ON TBL_Holidays.AgentRef = " _
& "TBL_Agents.AgentRef INNER JOIN TBL_TeamsTracker ON TBL_Agents.AgentRef = " _
& "TBL_TeamsTracker.AgentRef INNER JOIN TBL_Teams ON TBL_TeamsTracker.TeamRef " _
& "= TBL_Teams.TeamRef WHERE (TBL_TeamsTracker.EndDate IS NULL) AND " _
& "('" & ChosenDate & "' BETWEEN TBL_Holidays.ActivityDate AND (DATEADD(d, CASE " _
& "DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate) + (DATEPART(dw, DATEADD(d, CASE DATEPART(dw, " _
& "ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate)) - 2 + CAST(TBL_Holidays.TimeTaken / " _
& "(TBL_Agents.ContractHrs / 5) AS Int)) % 5 + (DATEPART(dw, DATEADD(d, " _
& "CASE DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 ELSE 0 END, " _
& "TBL_Holidays.ActivityDate)) - 2 + CAST(TBL_Holidays.TimeTaken / " _
& "(TBL_Agents.ContractHrs / 5) AS Int)) / 5 * 7) - (DATEPART(dw, " _
& "DATEADD(d, CASE DATEPART(dw, ActivityDate) WHEN 7 THEN 2 WHEN 1 THEN 1 " _
& "ELSE 0 END, TBL_Holidays.ActivityDate)) - 2)) AND " _
& "(TBL_Agents.Location = N'" & lblLocation.Text & "') " _
& "ORDER BY TBL_Holidays.ActivityDate DESC, TBL_Agents.LastName, " _
& "TBL_Holidays.Status"

I thank you all for taking the time to assist me with this matter, your comments did make me look more closely and as a result stumble across the solution.

I have now come to the conlcusion that 10 cups of coffee are no substitute for a good nights sleep when trying to code stuff :)

Thanks Again

Andrew

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:
>