Comparing Datetime SQL server and asp.net

Question

I'm making a function that would cancel an order only if it's within 60 minutes, what I was suggested is to compare the datetime value of the order to the current datetime.

        using (SqlConnection con = new SqlConnection(Helper.GetCon()))
        {
            con.Open();
            string query = @"SELECT * from [Order] where orderid = @id";
            using (SqlCommand cmd = new SqlCommand(query, con))
            {
                cmd.Parameters.AddWithValue("@id", id);
                using (SqlDataReader data = cmd.ExecuteReader())
                {
                    DataTable dt = new DataTable();
                    dt.Load(data);
                    foreach (DataRow dr in dt.Rows)
                    {
                        var supplier = new Supplier();
                        supplier.OrderDate = DateTime.Parse(dr["OrderDate"].ToString());
                    }
                }
            }
            return View();

So far I've only taken the datetime from the database, I was wondering how you can get the current datetime and get the difference in let's say minutes or hours so that I can make a conditional statement to allow or not allow the cancellation of an order. I'm fairly new to this and any help would be appreciated! Thank you!

Are you looking to filter this data in SQL Server, or your application (using C#)? — Thom A
– Thom A ♦, Commented Mar 13, 2019 at 16:49
See msdn : learn.microsoft.com/en-us/sql/t-sql/functions/… — jdweng
– jdweng, Commented Mar 13, 2019 at 16:55

L0uis · Accepted Answer · 2019-03-13 16:54:38Z

2

Use DateDiff, comparing the selected date column with now and use the MINUTE difference:

SELECT DATEDIFF(MINUTE, OrderDate , getdate()) AS MinuteDiff from [Order] where orderid =123

answered Mar 13, 2019 at 16:54

L0uis

7295 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Andrew · Accepted Answer · 2019-03-13 17:05:18Z

0

If you must do it in the application rather than the SQL query, you can do something like:

var timeSinceOrder = DateTime.Now - supplier.OrderDate;

if(timeSinceOrder < TimeSpan.FromHours(1))
{
    //do stuff here
}

DateTime.Now gets the current time. Subtracting the order date from the current time returns a timespan that you can then compare to another timespan, in this case created by the function Timespan.FromHours.

answered Mar 13, 2019 at 17:05

Andrew

162 bronze badges

1 Comment

Kurise Over a year ago

Hey @Andrew! Thank you for the help. I tried the code and it worked flawlessly. Cheers!

Jonathon Chiu · Accepted Answer · 2019-03-13 20:50:13Z

0

You can also use DateTime.Compare method. Something like

If(DateTime.Compare(supplier.OrderDate, DateTime.Now) < 60)
{  
   //Enable Cancel/Delete Order
}

answered Mar 13, 2019 at 20:50

Jonathon Chiu

12 bronze badges

Collectives™ on Stack Overflow

Comparing Datetime SQL server and asp.net

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related