使用datetime2在今天的SQL Server查询

I have a SQL Server database that when I open it on screen I want it to just show the records that have been added today.

我有一个SQL Server数据库,当我在屏幕上打开它时,我希望它只显示今天添加的记录。

I have the date stored in a field called DateAdded and it is of DateTime2 data type.

我将日期存储在名为DateAdded的字段中,它是DateTime2数据类型。

I tried this

我试过这个

string dateAdded = today.ToShortDateString();

string query = "SELECT CustomerID, Title, FirstName, LastName, AppStatus 
FROM Customer     
WHERE DateAdded ='" + dateAdded + "'";

This displays no records which I would assume as it has the time attribute stored with the date.

这显示没有我会假设的记录,因为它具有与日期一起存储的时间属性。

I searched online to try and find the answer, but none seemed to be specific for just records added today.

我在网上搜索试图找到答案,但似乎没有一个特定于今天添加的记录。

I'm also going to need to search on date ranges based on user inputs, and it could be on any range for eg view records from 09/01/2013 to 18/03/2013.

我还需要根据用户输入搜索日期范围,它可以在任何范围内,例如从2013年9月1日到2013年3月18日的视图记录。

What is the best way to go about writing the sql query for this?

为此编写SQL查询的最佳方法是什么?

EDIT - My Answer

编辑 - 我的答案

The answer Carl gave worked, but I didn't understand how to change the values so I could have different date ranges. I though I should post my final result on here, which I based on the answers I received, so it might help someone else. :)

Carl给出的答案有效,但我不明白如何更改值,以便我可以有不同的日期范围。我虽然我应该在这里发布我的最终结果,我根据我收到的答案,所以它可能会帮助别人。 :)

using (SqlConnection conn = new SqlConnection(connectionString))
        {
            int year = date1.Year;
            int month = date1.Month;
            int day = date1.Day;

            string dateAdded = year + "-" + month + "-" + day + " ";

            int year2 = date2.Year;
            int month2 = date2.Month;
            int day2 = date2.Day;

            string dateAdded2 = year2 + "-" + month2 + "-" + day2 + " ";

            string query = "SELECT CustomerID, Title, FirstName, LastName, AppStatus, DateAdded FROM Customer WHERE DateAdded >= @date1 AND DateAdded <= @date2";

            using (SqlCommand cmd = new SqlCommand(query, conn))
            {
                cmd.Parameters.AddWithValue("date1", dateAdded + "00:00:00");
                cmd.Parameters.AddWithValue("date2", dateAdded2 + "23:59:59");
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(holder);
            }
        }

3 个解决方案

#1

From the OP's responses changed the SQL to this

从OP的响应中将SQL更改为此

DECLARE @StartDate DATETIME2, @EndDate DATETIME2

SET @StartDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
SET @EndDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()+1), 0)

SELECT CustomerID, Title, FirstName, LastName, AppStatus 
    FROM Customer     
    WHERE DateAdded >= @StartDate AND DateAdded < @EndDate

This will get anything on the current date (you can replace Getdate() with a date of your choice for re-usability)

这将获得当前日期的任何内容(您可以使用您选择的日期替换Getdate()以获得可重用性)

#2

How about this one:

这个怎么样:

var sqlCommand = new SqlCommand();
sqlCommand.CommandText = "SELECT CustomerID, Title, FirstName, LastName, AppStatus FROM Customer WHERE DateAdded >= @DateAdded";
sqlCommand.Parameters.AddWithValue("@DateAdded", DateTime.Today);

#3

-1

Try your SQL query like below..

尝试下面的SQL查询..

Query:

string query = "SELECT CustomerID, Title, FirstName, LastName, AppStatus FROM Customer
WHERE CONVERT(VARCHAR(10), dateAdded, 101) = CONVERT(VARCHAR(10), @DateAdded, 101)";

Parametized Command :

参数化命令:

string dateAdded = today.ToShortDateString();
SqlCommand cmd = new SqlCommand();
cmd.CommandText = query ;
cmd.Parameters.AddWithValue("@DateAdded", dateAdded);

#1