where yourfieldname >= '2009-01-01' and yourfieldname < '2010-01-01' 按年查where yourfieldname >= '2009-01-01' and yourfieldname < '2010-02-01' 按月查
此法可以,也可以是WEEK,HOUR
我想在C#中调用,如只是输入一个年 2009 就查询相关数据.用like可以吗?那应该怎么写呢`?
你的年,月,日是在一个textbox中输入的么? 如果不是的,用year,month,day where datediff(year,字段,'2009-03-20 18:07:59')=textbox的值 如果是的 用like的话查年管用,查月,日貌似就不准确了
对~想在textbox中输入,,,, 嗯嗯`~了解了解~
2009-03-20 18:07:59 方法一: 时间字段 between '2009-03-20 00:00:00' and '2009-03-20 23:59:59' 方法二: datepart(dd,时间字段)=20 and datepart(mm,时间字段)=3 and datepart(mm,时间字段)=2009都可以查询出2009-03-20当天的信息
/* 按年查询 */ SELECT * FROM [TABLE] WHERE YEAR(GETDATE()) = '2009' SELECT * FROM [TABLE] WHERE DATEPART(YEAR,GETDATE()) = 2009 SELECT * FROM [TABLE] WHERE DATENAME(YEAR,GETDATE()) = '2009' SELECT * FROM [TABLE] WHERE DATEDIFF(YEAR,[字段],GETDATE()) = 0/* 按月查询 */ SELECT * FROM [TABLE] WHERE MONTH(GETDATE()) = '4' SELECT * FROM [TABLE] WHERE DATEPART(MONTH,GETDATE()) = 4 SELECT * FROM [TABLE] WHERE DATENAME(MONTH,GETDATE()) = '04' SELECT * FROM [TABLE] WHERE DATEDIFF(MONTH,[字段],GETDATE()) = 0/* 按日查询 */ SELECT * FROM [TABLE] WHERE DAY(GETDATE()) = '1' SELECT * FROM [TABLE] WHERE DATEPART(DAY,GETDATE()) = 1 SELECT * FROM [TABLE] WHERE DATENAME(DAY,GETDATE()) = '01' SELECT * FROM [TABLE] WHERE DATEDIFF(DAY,[字段],GETDATE()) = 0注意:其中按月查询和按日查询时要注意年份也要相等!
按时间段 between '2009-03-20 00:00:00' and '2009-03-20 23:59:59' 按年 select * from table where Year(getdate()) = '2009' 按月 select * from table where Month(getdate())='4' 按日 select * from table where Day(getdate())='2'
用convert(yyyy-MM,字段)即从字段中提出年月,,同样dd表示几日
按时间段 between '2009-03-20 00:00:00' and '2009-03-20 23:59:59' 这样的话不行的~时间段超过十天以上的话
此法可以,也可以是WEEK,HOUR
如果不是的,用year,month,day
where datediff(year,字段,'2009-03-20 18:07:59')=textbox的值
如果是的
用like的话查年管用,查月,日貌似就不准确了
对~想在textbox中输入,,,,
嗯嗯`~了解了解~
方法一:
时间字段 between '2009-03-20 00:00:00' and '2009-03-20 23:59:59'
方法二:
datepart(dd,时间字段)=20 and datepart(mm,时间字段)=3 and datepart(mm,时间字段)=2009都可以查询出2009-03-20当天的信息
2009-03-20 18:07:59>convert(datetime,'2009')
http://www.soidc.net/articles/1183730975605/20050408/1214040078954_1.html
SELECT * FROM [TABLE] WHERE YEAR(GETDATE()) = '2009'
SELECT * FROM [TABLE] WHERE DATEPART(YEAR,GETDATE()) = 2009
SELECT * FROM [TABLE] WHERE DATENAME(YEAR,GETDATE()) = '2009'
SELECT * FROM [TABLE] WHERE DATEDIFF(YEAR,[字段],GETDATE()) = 0/* 按月查询 */
SELECT * FROM [TABLE] WHERE MONTH(GETDATE()) = '4'
SELECT * FROM [TABLE] WHERE DATEPART(MONTH,GETDATE()) = 4
SELECT * FROM [TABLE] WHERE DATENAME(MONTH,GETDATE()) = '04'
SELECT * FROM [TABLE] WHERE DATEDIFF(MONTH,[字段],GETDATE()) = 0/* 按日查询 */
SELECT * FROM [TABLE] WHERE DAY(GETDATE()) = '1'
SELECT * FROM [TABLE] WHERE DATEPART(DAY,GETDATE()) = 1
SELECT * FROM [TABLE] WHERE DATENAME(DAY,GETDATE()) = '01'
SELECT * FROM [TABLE] WHERE DATEDIFF(DAY,[字段],GETDATE()) = 0注意:其中按月查询和按日查询时要注意年份也要相等!
between '2009-03-20 00:00:00' and '2009-03-20 23:59:59'
按年
select * from table where Year(getdate()) = '2009'
按月
select * from table where Month(getdate())='4'
按日
select * from table where Day(getdate())='2'
between '2009-03-20 00:00:00' and '2009-03-20 23:59:59'
这样的话不行的~时间段超过十天以上的话