I work on an ASP.NET web forms project; I can't get data between two times (time from and time to) on datetime column.
C#function
public DataTable GetDataForSearch(string datelogged, string FromTime, string ToTime)
{
string response = string.Empty;
SqlCommand cmd = new SqlCommand();
DataTable dt = new DataTable();
try
{
conn.Open();
cmd.Connection = conn;
cmd.CommandText = "select datelogged AS EntredDatetime, Doc_type AS OrderType, Printer_name, BranchID AS BranchCode, Status, id from Print_Report where cast(datelogged as date)=@datelogged and and FORMAT(CAST(datelogged AS DATETIME), 'HH:mm')>'@FromTime' AND FORMAT(CAST(datelogged AS DATETIME), 'HH:mm')<@ToTime";
cmd.CommandType = CommandType.Text;
cmd.CommandTimeout = 50000;
cmd.Parameters.AddWithValue("@datelogged", datelogged);
cmd.Parameters.AddWithValue("@FromTime", FromTime);
cmd.Parameters.AddWithValue("@ToTime", ToTime);
SqlDataAdapter sda = new SqlDataAdapter(cmd);
sda.Fill(dt);
}
catch (Exception ex)
{
response = ex.Message;
}
finally
{
cmd.Dispose();
conn.Close();
}
return dt;
}
When I try from SQL Server, it return 2 rows:
select
datelogged as EntredDatetime, Doc_type as OrderType,
Printer_name, BranchID as BranchCode, Status, id
from
Print_Report
where
BranchID = '10207'
and cast(datelogged as date) = '2010-07-05'
and Doc_type = 'BP'
and format(cast(datelogged as DATETIME), 'HH:mm') > '13:30'
and format(cast(datelogged as DATETIME), 'HH:mm') < '14:00'
Expected result:
I modified query to as below :
cmd.CommandText = "select datelogged AS EntredDatetime, Doc_type AS OrderType, Printer_name, BranchID AS BranchCode, Status, id from Print_Report where BranchID=@BranchCode and cast(datelogged as date)=@datelogged and Doc_type=@OrderType and FORMAT(CAST(datelogged AS DATETIME), 'HH:mm')>='@FromTime' AND FORMAT(CAST(datelogged AS DATETIME), 'HH:mm')<='@ToTime'";
but still not get any result so what i do to solve issue
CodePudding user response：
Always declare the type of parameter you're passing into SQL. Don't rely on sql to infer it. Also make a habit of wrapping your db connection in a Using block.
cmd.Parameters.Add("@datelogged", sqldbtype.datetime).value = datelogged;
cmd.Parameters.Add("@FromTime", sqldbtype.time).value = FromTime;
cmd.Parameters.Add("@ToTime", Sqldbtype.time).value = ToTime;
You'll need to add code for the @branch and @ordertype parameters as well.
Your SQL would look like the below. When writing SQL, most mistakes can be managed by consistently formatting your SQL while you write it. Parameters do not need to be wrapped in quotes when you declare the data type of the param like I have above. It handles all that for you. Rather than comparing the date separately, I opted to pass the times in as Time data type and then cast them to datetime inside the query. From there, you can add two date times together with the and then do your comparison the same way. If you decide to pass your to/from values in as datetime then just remove the declare line at the top.
You could use BETWEEN in the WHERE clause but that's at your discretion.
DECLARE @To DATETIME = (@datelogged CAST(@ToTime AS DATETIME)), @From DATETIME = (@datelogged CAST(@FromDate AS DATETIME));
SELECT
datelogged AS EntredDatetime,
Doc_type AS OrderType,
Printer_name,
BranchID AS BranchCode,
Status,
ID
FROM Print_Report
WHERE BranchID = @BranchCode AND
Doc_type = @OrderType AND
datelogged >= @From AND
datelogged <= @To
CodePudding user response：
So, a few things:
Don't wrap long lines of code, they are VERY prone to errors.
Looking at your SQL, you have this:
select datelogged AS EntredDatetime, Doc_type AS OrderType, Printer_name,
BranchID AS BranchCode, Status, id from Print_Report
where cast(datelogged as date)=@datelogged
and and FORMAT(CAST(datelogged AS DATETIME), 'HH:mm')>'@FromTime'
AND FORMAT(CAST(datelogged AS DATETIME), 'HH:mm')<@ToTime"
Note how easy now we see "and and" in your SQL.
And don't do those casts in the SQL, since it messy, but such expressions can't be indexed, and they will run turtle slow. (besides, it makes the SQL rather a mess to work with).
next up: consider moving the in-line SQL out to a stored procedure, but even better (and less effort and work) is to use a view, and thus "less" work then a stored procedure, you can use the query builder/designer, and EVEN better is multiple routines can use that one "view". You still use in-line SQL, but only say
select * from vPrint_Report
WHERE datelogged is BETWEEN @dtStart AND @dtEnd";
next up:
Don't use add with value - use STRONG typed parameter's.
next up:
While using Parmaters.Add("some parm", "some value") is deprecated, since the 2nd parameter can be confused with int as dbtype?
using typed parameter with "ADD" IS NOT deprecated!!!, and in fact I recommend using
.ADD("@ParmName", sqlDbType.int).Value =
Again, above format is NOT deprecated, only the overload with a non dbtype for 2nd parameter in above is deprecated!!!
Next up:
You don't show where that connection object is created. DO NOT try to make some global scoped connection object. While before the "web days", for performance, yes, often we "persisted" a connection object, but with web based, there is a automatic "connection" pool, and thus the standard is to re-create the connection's each time, and LET THE SYSTEM AUTOMATIC dispose for you. By doing this, you leverage the "automatic" connection system in .net web based system.
Re-creating the connection object each time is NOT a performance hit, since the connection pool will "find" and use cached connection - it runs fast - feel free to re-create connection object each time.
And how do you LET the system manage this for you? Why of course you ALWAYS wrap that code in using blocks. In your example, you ONLY dispose of the connection when code errors, but not on success.
next up:
As noted, don't try to parse out, or convert the date, and date time in the SQL, but supply STRONG types for start and end date (both with time). Not only is such code less of a mess, but it also a "tiny" bit of work in the code, but the SQL becomes VAST VAST less messy. So, we trade a "wee bit" of code for a HUGE bonus of nice clean SQL.
So, lets take all of the above lessons, and thus we get this now:
public DataTable GetDataForSearch(string datelogged, string FromTime, string ToTime)
{
DataTable dt = new DataTable();
string strCon = Properties.Settings.Default.TEST4; // change to YOUR conneciton
using (SqlConnection conn = new SqlConnection(strCon))
{
string strSQL =
@"select datelogged AS EntredDatetime, Doc_type AS OrderType,
Printer_name, BranchID AS BranchCode, Status, id
FROM Print_Report
WHERE datelogged is BETWEEN @dtStart AND @dtEnd";
DateTime dtDate = DateTime.Parse(datelogged);
DateTime dtFromTime = DateTime.Parse(FromTime);
DateTime dtToTime = DateTime.Parse(ToTime);
DateTime dtStart =
dtDate.Add(new TimeSpan(dtFromTime.Hour, dtFromTime.Minute, dtFromTime.Second));
DateTime dtEnd =
dtDate.Add(new TimeSpan(dtToTime.Hour, dtToTime.Minute, dtToTime.Second));
using (SqlCommand cmd = new SqlCommand(strSQL, conn))
{
cmd.Parameters.Add("@dtStart", SqlDbType.DateTime).Value = dtStart;
cmd.Parameters.Add("@dtEnd", SqlDbType.DateTime).Value = dtEnd;
try
{
conn.Open();
dt.Load(cmd.ExecuteReader());
}
}
}
return dt;
}
So, note how we let the system "close" the connection's, and dispose of it. And even if the code errors out, even when NOT trapped, the connection and command object will be correctly disposed and managed for you - IN ALL cases!!!
Also, note how a bit of effort on the code side to get STRONG TYPED date start and end, thus makes the SQL part a whole lot less work, but MORE important also means that we use enjoy STRONG typed values for the parameter's, and we enjoy use of high speed indexing.