将当前日期与数据库中的日期进行比较 [英] compare current date with a date in database

问题描述

大家好，



我正在尝试为我的网站实施一个应用程序，在那里我想要将产品价格降低20％一年多。



所以我有一个数据表产品列[id，name，price，date]

，代码如下：检索当前日期，然后我尝试从我的

sql表产品中检索日期并与今天的日期进行比较，但我现在不知道如何比较它们并确定它是否是旧产品？

这是我正在尝试这样做的正确方法吗？

为了解决这个问题我应该改变什么？



Thnx提前!!!

Hi guys,

I'm trying to implement an application for my web site , where I want to discount products 20% in their price which they are older than a year.

So I have a datatable products with columns [id,name,price,date]
with the following code i retrieve the current date and then I try to retrieve the date from my
sql table products and compare with the today date , but i don't now how to compare them and decide if that is an old product?
is this the correct way where i'm trying to do this?
what i shoud change in order to work this?

Thnx in advance!!!

string todaydate = DateTime.Now.ToString("dd/MM/yyyy");

 SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

        string aSql = "select date from products";
         SqlCommand sqlCommand = new SqlCommand(aSql, con);
     
      
            con.Open();
            using (SqlDataReader read = sqlCommand.ExecuteReader())
            {
                while (read.Read())
                {
                    string date2 = read["date"].ToString();
                    string price = read["price"].ToString();

                    if(todaydate - date2 > )//12months i must writte something here)
                    {
                        int price2 = Convert.ToInt32(price) - ((20/100)*Convert.ToInt32(price));
                    }
                }
                GridView1.DataSource = read;
                GridView1.DataBind();
                read.Close();
                
            }
            con.Close();

推荐答案

您可以添加到查询中DateDiff函数。

so：

You can add to the query the DateDiff function.
so:

select date from products
where DateDiff(m, date, GetDate()) < 13

在这种情况下，DATE字段和当前日期 - > GetDate（）函数在查询结果中返回。您已在选择中解决了您的问题。不需要使用TimeSpan对象进行后处理。

In this case the differences in monthes between the DATE field and the current date ->GetDate() function is returnen in the result from the query. You have solved your problem in the select. No postprocessing with TimeSpan object is needed.

digimanus的解决方案1非常好，但我建议你使用存储过程。有关详细信息，请参阅：带有文本字段C＃的SQL SELECT语句.NET [ ^ ]

Solution 1 by digimanus is very good, but i would suggest you to use stored procedure. For further information, please see: SQL SELECT statement with text field C# .NET[^]

SELECT [date], price, price * 20/100 AS NewPrice
FROM products
WHERE DateDiff(yy, [date], GETDATE()) = 1

更多： DATEDIFF [ ^ ]

你可以使用 DATEDIFF（）函数。



例如

You can use DATEDIFF() function for that.

E.g.

SELECT DATEDIFF(day,'2008-06-05','2008-08-05') AS DiffDate

结果：DiffDate = 61



欲了解更多信息：SQL Server DATEDIFF（）函数

这篇关于将当前日期与数据库中的日期进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！