在两天到两次之间从两个sql server列中选择Records [英] Select Records from two sql server columns between two days and two times

问题描述

你好。



i有两列订购日和ordertime



i希望从10-获取记录08-2013和时间12:00至11-08-2013和时间01:00



http://i42.tinypic.com/2mq26ua.jpg [ ^ ]

Hello.

i have two columns orderday And ordertime

i want to get records from 10-08-2013 and time 12:00 to 11-08-2013 and time 01:00

http://i42.tinypic.com/2mq26ua.jpg[^]

DataSet dsaa = new DataSet();
               SqlConnection csaa = new SqlConnection("workstation id=;initial catalog=iridadb; integrated security=SSPI");
               SqlDataAdapter daaa = new SqlDataAdapter();

               daaa.SelectCommand = new SqlCommand("SELECT * FROM Orders  WHERE orderday >'" + Convert.ToDateTime(dateTimePicker1.Text).AddDays(-1).Date.ToString("yyyy-MM-dd") + "' AND orderday < '" + Convert.ToDateTime(dateTimePicker2.Text).AddDays(1).Date.ToString("yyyy-MM-dd") + "' or  ordertime >  '" + txtTime1.Text + "' AND ordertime < '" + txtTime2.Text + "'  ORDER BY orderid ASC", csaa);

               dsaa.Clear();

               daaa.Fill(dsaa);
               gridControl1.DataSource = dsaa.Tables[0];
               gridView1.MoveLast();

请帮我

pls help me

推荐答案

首先，永远不要那样做！不要连接字符串以构建SQL命令。它让您对意外或故意的SQL注入攻击持开放态度，这可能会破坏您的整个数据库。请改用参数化查询。这有一个额外的好处，你不必将日期转换为字符串，使你的代码更容易阅读。



其次，你想要的SQL是BETWEEN：

First off, never do it like that! Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead. This has the added advantage of you not having to convert dates to strings and making your code a lot more readble.

Second, The SQL you want is BETWEEN:

SELECT * FROM myTable WHERE myDateTime BETWEEN '2013-03-01 00:00' AND '2013-03-01 01:00'

如果使用参数化查询，则使用 DateTime.Date [ ^ ]属性丢弃任何时间元素，然后使用 DateTime.AddHo urs [ ^ ]方法将它推进到你想要的时间。

If you use a Parameterised query, then use the DateTime.Date[^] property to discard any time element, then use DateTime.AddHours[^] method to advance it to the time you want.

请阅读OriginallGriff的答案。这非常重要！



为了帮助您从查询中获取正确的值，请参阅以下示例：

Please, read OriginallGriff's answer. It's very important!

To help you to get proper values from query, please see below example:

DECLARE @orders TABLE (ID INT IDENTITY(1,1), orderday VARCHAR(30), ordertime VARCHAR(30))

INSERT INTO @orders (orderday, ordertime)
SELECT '2013-06-30', '09:26'
UNION ALL SELECT '2013-07-01', '19:06'
UNION ALL SELECT '2013-07-30', '08:35'
UNION ALL SELECT '2013-08-01', '11:26'
UNION ALL SELECT '2013-08-10', '09:26'
UNION ALL SELECT '2013-08-10', '19:53'
UNION ALL SELECT '2013-08-10', '20:20'
UNION ALL SELECT '2013-08-11', '06:46'
UNION ALL SELECT '2013-08-11', '13:05'
UNION ALL SELECT '2013-08-11', '17:00'


SELECT *
FROM (
	SELECT ID, CONVERT(DATETIME, orderday + ' ' + ordertime + '.000') AS orderdate 
	FROM @orders
)AS T
WHERE orderdate BETWEEN '2013-08-10 12:00:00.000' AND '2013-08-11 01:00:00.000'

结果：

Result:

ID      orderdate
6	2013-08-10 19:53:00.000
7	2013-08-10 20:20:00.000

注意：我正在使用MS SQL Server 2005.因此，在上面的例子中， orderday 字段是 varchar 数据类型字段，因为此版本的MS SQL服务器不支持日期数据类型。有关详细信息，请参阅：

数据类型（Transact-SQL） [ ^ ]

CAST和CONVERT（T-SQL） [ ^ ]

Note: i'm working on MS SQL server 2005. So, in above example the orderday field is varchar data type field, because this version of MS SQL server does not supports date data type. For further information, please, see:
Data Types (Transact-SQL)[^]
CAST and CONVERT (T-SQL)[^]

这篇关于在两天到两次之间从两个sql server列中选择Records的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！