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.
