question

Reborn-0047 avatar image
0 Votes"
Reborn-0047 asked Reborn-0047 edited

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

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

dotnet-aspnet-webapi
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AgaveJoe avatar image
0 Votes"
AgaveJoe answered

Use the SQL BETWEEN

 WHERE @search BETWEEN create_date AND modify_date


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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

FROM tblTest WHERE datetimefromparts(@search, create_date) = 1";

And what's datetimefromparts here? By the passed parameter it don't look like the standard T-SQL Function.
https://docs.microsoft.com/en-us/sql/t-sql/functions/datetimefromparts-transact-sql?view=sql-server-ver16


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LanHuang-MSFT avatar image
0 Votes"
LanHuang-MSFT answered

Hi @Reborn-0047,
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);
         }

227086-image001.png
227143-image002.png
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.



image001.png (8.1 KiB)
image002.png (127.6 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

satyakarki-5933 avatar image
0 Votes"
satyakarki-5933 answered

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



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.