17,740
社区成员
发帖
与我相关
我的任务
分享
//方法一,NET3.5中用LINQ
namespace CSharp
{
public class Program
{
static void Main(string[] args)
{
DataTable dt = new DataTable();
dt.Columns.Add("DateTimeNow",typeof(DateTime));
DateTime td = new DateTime(2012,4,1,7,0,0);
Random r = new Random(Guid.NewGuid().GetHashCode());
for (int i = 1; i < 21; i++)
{
dt.Rows.Add(td);
td=td.AddDays(1);
td=td.AddHours(r.Next(1,3));
Thread.Sleep(5);
//输出原来的日期
Console.WriteLine(dt.Rows[i-1][0]);
}
Console.WriteLine("============");
var result = from row in dt.AsEnumerable()
let date = DateTime.Parse(row[0].ToString()).Date
let time = DateTime.Parse(row[0].ToString()).TimeOfDay
where date >= new DateTime(2012, 4, 1) && date <= new DateTime(2012, 4, 10) &&
time >= new TimeSpan(8, 0, 0) && time <= new TimeSpan(9, 0, 0)
select row;
foreach (var item in result)
{
Console.WriteLine(item[0].ToString());
}
}
}
}
//方法二
namespace MyTest
{
public class TestArray
{
public static void Main()
{
DataTable dt = new DataTable();
dt.Columns.Add("DateTimeNow", typeof(DateTime));
DataTable cdt = dt.Clone(); //拷贝副本
DateTime td = new DateTime(2012, 4, 1, 7, 0, 0);
Random r = new Random(Guid.NewGuid().GetHashCode());
for (int i = 1; i < 21; i++)
{
dt.Rows.Add(td);
td = td.AddDays(1);
td = td.AddHours(r.Next(1, 3));
Thread.Sleep(5);
//输出原来的日期
Console.WriteLine(dt.Rows[i - 1][0]);
}
Console.WriteLine("============");
//先过滤日期
DataView dv = new DataView(dt);
dv.RowFilter = "DateTimeNow>='2012/04/01 00:00:00' And DateTimeNow<='2012/04/10 23:59:59'";
dt = dv.ToTable();
//再过滤时间
foreach (DataRow item in dt.Rows)
{
DateTime dtt = DateTime.Parse(item[0].ToString());
if (dtt.TimeOfDay >= new TimeSpan(8, 0, 0) && dtt.TimeOfDay <= new TimeSpan(9, 0, 0))
{
cdt.Rows.Add(dtt);
}
}
//输出
foreach (DataRow item in cdt.Rows)
{
Console.WriteLine(item[0].ToString());
}
}
}
}
DataSet dateds = new DataSet();
string constring = @"server=YKZ-20110406NYO\SQLEXPRESS;DataBase=master;User ID=sa;Password=abc12345";
SqlConnection conn = new SqlConnection(constring);
string sql = @"SELECT * FROM testDate WHERE (SUBSTRING(CONVERT(VARCHAR,DateLoan,120),1,10) >='2012-04-27' and SUBSTRING(CONVERT(VARCHAR,DateLoan,120),1,10)<='2012-04-28') and (SUBSTRING(CONVERT(VARCHAR,DateLoan,114),1,8) >= '08:00:00' and SUBSTRING(CONVERT(VARCHAR,DateLoan,114),1,8)<='09:00:00')
";
SqlDataAdapter adpter = new SqlDataAdapter(sql, conn);
adpter.Fill(dateds, "testDate");
DataTable dtdate = dateds.Tables["testDate"];
create table testDate
(
ID int primary key identity(1,1),
DateLoan datetime
)
insert into testDate values('2012-4-27 07:20:10')
insert into testDate values('2012-4-28 07:20:10')
insert into testDate values('2012-4-28 08:20:10')
insert into testDate values('2012-4-28 08:30:10')
insert into testDate values('2012-4-28 08:40:10')
insert into testDate values('2012-4-28 08:50:10')
insert into testDate values('2012-4-28 09:00:00')
insert into testDate values('2012-4-28 09:20:10')
insert into testDate values('2012-4-28 09:20:10')
SELECT * FROM testDate
/*
ID DateLoan
----------- -----------------------
1 2012-04-28 07:20:10.000
2 2012-04-28 08:20:10.000
3 2012-04-28 08:30:10.000
4 2012-04-28 08:40:10.000
5 2012-04-28 08:50:10.000
6 2012-04-28 09:00:00.000
7 2012-04-28 09:20:10.000
8 2012-04-28 09:20:10.000
9 2012-04-27 07:20:10.000
(9 行受影响)
*/
SELECT * FROM testDate WHERE SUBSTRING(CONVERT(NVARCHAR(20),DateLoan,120),1,10) between '2012-04-27' and '2012-04-28' and SUBSTRING(CONVERT(NVARCHAR(20),DateLoan,114),1,8) between '08:00:00' and '09:00:00'
/*
ID DateLoan
----------- -----------------------
2 2012-04-28 08:20:10.000
3 2012-04-28 08:30:10.000
4 2012-04-28 08:40:10.000
5 2012-04-28 08:50:10.000
6 2012-04-28 09:00:00.000
(5 行受影响)
*/