Use the SQL BETWEEN
WHERE @search BETWEEN create_date AND modify_date
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
i have some question about getting date from previous between create_date and modify_date, i am working on asp.net web api 2 and i want to seach the date between these two dates from sql server, there are many records in database. The result outcome is all the records in Create_date. It is not my expect result, how can i make it to work like what i want?
my qestion is how to seach specific create_date between create_date and modify_date? not only for one record.
example:
localhost:IP/api/values?create_date=2019-01-01
OR
localhost:IP/api/values?create_date=2010-01-01
expect result outcome:
[
{
"create_date": "2019-01-01T23:00:00",
"modify_date": "2021-10-01T10:00:00"
},
{
"create_date": "2018-02-08T00:00:00",
"modify_date": "2020-12-10T13:00:00"
},
{
"create_date": "2021-09-21T21:00:00",
"modify_date": "2022-03-05T15:00:00"
}
]
OR
[
{
"create_date": "2010-10-12T12:00:00",
"modify_date": "2017-01-29T20:00:00"
},
{
"create_date": "2008-05-07T12:00:00",
"modify_date": "2022-07-01T14:00:00"
},
{
"create_date": "2009-10-20T17:00:00",
"modify_date": "2015-07-07T19:00:00"
}
]
my code which is not working:
public IHttpActionResult Get(string search)
{
List<TestClass> draft = new List<TestClass>();
string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
using (SqlConnection sqlconn = new SqlConnection(mainconn))
{
string sqlquery = "SELECT UserID, Name, Mobile, Access, Create_Date, Modify_Date
FROM tblTest WHERE datetimefromparts(@search, create_date) = 1";
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
sqlcomm.Parameters.AddWithValue("@search", search);
SqlDataReader sdr = sqlcomm.ExecuteReader();
while (sdr.Read())
{
draft.Add(new TestClass()
{
UserId = sdr.GetString(0),
Name = sdr.GetString(1),
Mobile = sdr.GetString(2),
Access = (sdr.GetValue(3) != DBNull.Value) ? Convert.ToInt32(sdr.GetValue(3)) : 0,
Create_Date = (sdr.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(4)) : (DateTime)sdr.GetValue(4),
Modify_Date= (sdr.GetValue(5) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(5)) : (DateTime?)null
});
}
}
return Ok(draft);
}
Is that possible? Maybe i might not said it clear in the question, please write in the comment, i will try to explain what i want, thank you
Use the SQL BETWEEN
WHERE @search BETWEEN create_date AND modify_date
FROM tblTest WHERE datetimefromparts(@検索君 , create_date) = 1";
And what's datetimefromparts here? By the passed parameter it don't look like the standard T-SQL Function.
https://zcusa.951200.xyz/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver16
Hi @Reborn ,
My idea is to use the two variables as constants through the string.format method, and then enter 07-01 and 07-03 in the URL to filter Create_Date data between July 1st and July 3rd.
string sqlquery =string.Format ( " SELECT UserID, Name, Mobile, Access, Create_Date, Modify_Date FROM tblTest WH ERE Create_Date Between '{0}' AND '{1}'",Time1,Time2);
You can change the code to the following, Time1 is the start date, Time2 is the end date.
The test url is:https://localhost:44307/api/Test?Time1=2022-07-01 00:00:00.000&Time2=2022-07-03 00:00:00.000
public IHttpActionResult Get(DateTime Time1,DateTime Time2)
{
List<TestClass> draft = new List<TestClass>();
string mainconn = ConfigurationManager.ConnectionStrings["myconn"].ConnectionString;
using (SqlConnection sqlconn = new SqlConnection(mainconn))
{
string sqlquery =string.Format ( " SELECT UserID, Name, Mobile, Access, Create_Date, Modify_Date FROM tblTest WH ERE Create_Date Between '{0}' AND '{1}'",Time1,Time2);
sqlconn.Open();
SqlCommand sqlcomm = new SqlCommand(sqlquery, sqlconn);
SqlDataReader sdr = sqlcomm.ExecuteReader();
while (sdr.Read())
{
draft.Add(new TestClass()
{
UserId = sdr.GetString(0),
Name = sdr.GetString(1),
Mobile = sdr.GetString(2),
Access = (sdr.GetValue(3) != DBNull.Value) ? Convert.ToInt32(sdr.GetValue(3)) : 0,
Create_Date = (sdr.GetValue(4) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(4)) : (DateTime)sdr.GetValue(4),
Modify_Date = (sdr.GetValue(5) != DBNull.Value) ? Convert.ToDateTime(sdr.GetValue(5)) : (DateTime?)null
});
}
}
return Ok(draft);
}
Best regards,
Lan Huang
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hi, you can use between in your select query as below.
string sqlquery = "SELECT UserID, Name, Mobile, Access, Create_Date, Modify_Date
FROM tblTest WHERE @search BETWEEN Create_Date AND Modify_Date" ;