|M| Sql查询语名 where 关于时间问题 谢谢
如我要查询出 2007-03-01 到 2007-12-11日的下单记录我用
Select * from table where OrderDate > = '2007-03-01 ' and OrderDate <= '2007-12-11 '
是不是可以这样写?
但我又怕如果数据库的日期格式为 2007-3-1 这种情况的话不就完了
所以我想用
Select * from table where OrderDate between '2007-03-01 ' and '2007-12-11 '
这种方法
但同样会不会有上面所说的情况
还有
between '2007-03-01 ' and '2007-12-11 '
有没有包括了 '2007-03-01 ' 和 '2007-12-11 '这两天
谢谢
你们会怎么写
[解决办法]
OrderDate這個字段的類型是什麼？難道是varchar？
[解决办法]
where OrderDate > = '2007-03-01 ' and OrderDate < '2007-12-12 ' where orderdate between '2007-03-01 ' and < '2007-12-12 '或者where OrderDate > = '2007-03-01 ' and datediff(d,orderdate, ' '2007-12-11 ')> = 0
[解决办法]
或者where OrderDate > = '2007-03-01 ' and OrderDate <= '2007-12-11 23:59:59 '
[解决办法]
Select * from table where OrderDate > = '2007-03-01 ' and OrderDate <= '2007-12-11 '
是varchar就要转换cast(OrderDate as datetime )
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)
最新版本：20070212
http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
[解决办法]
between 相当于 > = 1 and <=2
[解决办法]
其實測試下就知道了
Create Table TEST(OrderDate DateTime)
Insert TEST Select '2007-02-01 '
Union All Select '2007-02-28 '
Union All Select '2007-03-01 '
Union All Select '2007-05-01 '
Union All Select '2007-12-11 '
Union All Select '2007-12-12 '
GO
Select * from TEST where OrderDate > = '2007-03-01 ' and OrderDate <= '2007-12-11 '
Select * from TEST where OrderDate between '2007-03-01 ' and '2007-12-11 '
GO
Drop Table TEST
--Result
/*
OrderDate
2007-03-01 00:00:00.000
2007-05-01 00:00:00.000
2007-12-11 00:00:00.000
OrderDate
2007-03-01 00:00:00.000
2007-05-01 00:00:00.000
2007-12-11 00:00:00.000
*/
[解决办法]
但是有一点.> = 没有问题 <=的时候 如 <= '2007-12-11 ' 只能到11日0点,即不含11日,
[解决办法]
如果你的字段只是需要用到日期，而時間沒有關係的話，兩種寫法是一樣的。
[解决办法]
如果是DateTime类型，就用慕白兄的方法，但如果包含当天：
where orderdate between '2007-03-01 00:00:00 ' and < '2007-12-12 23:59:59 '
如果存储的是字符串，则
where Convert(datetime,orderdate) between '2007-03-01 00:00:00 ' and < '2007-12-12 23:59:59 '
[解决办法]
zyciis137() ( ) 信誉：100 Blog 2007-03-05 15:50:10 得分: 0
是日期型的
对了可能OrderDate 他会有时间如
2007-12-11 15:49:44 要求这样也是要可以找得到的
谢谢
---------
回復之前沒有看到這個回復。如果是這樣的話，需要改下SQL語句
Create Table TEST(OrderDate DateTime)
Insert TEST Select '2007-02-01 12:00:00 '
Union All Select '2007-02-28 15:00:00 '
Union All Select '2007-03-01 18:00:00 '
Union All Select '2007-05-01 12:00:00 '
Union All Select '2007-12-11 22:00:00 '
Union All Select '2007-12-12 23:00:00 '
GO
Select * from TEST where OrderDate > = '2007-03-01 00:00:00 ' and OrderDate <= '2007-12-11 23:59:59 '
Select * from TEST where OrderDate between '2007-03-01 00:00:00 ' and '2007-12-11 23:59:59 '
GO
Drop Table TEST
--Result
/*
OrderDate
2007-03-01 18:00:00.000
2007-05-01 12:00:00.000
2007-12-11 22:00:00.000
OrderDate
2007-03-01 18:00:00.000
2007-05-01 12:00:00.000
2007-12-11 22:00:00.000
*/
