how to compare two dates and show in the link in web api?

Question

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 draft = new List();  
               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

Answer

Use the SQL BETWEEN

WHERE @search BETWEEN create_date AND modify_date

https://www.w3schools.com/sql/sql_between.asp

Answer

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://learn.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver16

Answer

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 draft = new List();  
            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.

Answer

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" ;

how to compare two dates and show in the link in web api?

4 answers