一个表中既有存放日期的字段又有存在时间的字段,如何查找既满足某个日期段又满足某个时间段的数据?
如
表hbiao字段如下: hm riqi shijian
char(8) char(10) char(10)
表中数据有
2220 2003-08-17 18:40:12
2221 2003-08-15 18:20:18
2222 2003-08-17 01:01:11
2223 2003-08-13 18:55:56
2224 2003-08-17 07:20:22
2225 2003-08-15 18:23:21
2226 2003-08-16 09:41:00
2227 2003-08-18 18:52:10
我要把2003-08-16日早晨8点以前数据查找出来,怎样查?
如
表hbiao字段如下: hm riqi shijian
char(8) char(10) char(10)
表中数据有
2220 2003-08-17 18:40:12
2221 2003-08-15 18:20:18
2222 2003-08-17 01:01:11
2223 2003-08-13 18:55:56
2224 2003-08-17 07:20:22
2225 2003-08-15 18:23:21
2226 2003-08-16 09:41:00
2227 2003-08-18 18:52:10
我要把2003-08-16日早晨8点以前数据查找出来,怎样查?
不好意思~应该是16号的
建议使用date,time or datetime类型
INSERT INTO @TempTable VALUES ('2221','2003-08-15','18:20:18')
INSERT INTO @TempTable VALUES ('2222','2003-08-17','01:01:11')
INSERT INTO @TempTable VALUES ('2223','2003-08-13','18:55:56')
INSERT INTO @TempTable VALUES ('2224','2003-08-17','07:20:22')
INSERT INTO @TempTable VALUES ('2225','2003-08-15','18:23:21')
INSERT INTO @TempTable VALUES ('2226','2003-08-16','09:41:00')
INSERT INTO @TempTable VALUES ('2227','2003-08-18','18:52:10') SELECT * FROM @TempTable WHERE CONVERT(DATETIME,riqi+' '+shijian)< '2003-08-16 08:00:00'结果:
hm riqi shijian
2221 2003-08-15 18:20:18
2223 2003-08-13 18:55:56
2225 2003-08-15 18:23:21
INSERT INTO @TempTable VALUES ('2221','2003-08-15','18:20:18')
INSERT INTO @TempTable VALUES ('2222','2003-08-17','01:01:11')
INSERT INTO @TempTable VALUES ('2223','2003-08-13','18:55:56')
INSERT INTO @TempTable VALUES ('2224','2003-08-17','07:20:22')
INSERT INTO @TempTable VALUES ('2225','2003-08-15','18:23:21')
INSERT INTO @TempTable VALUES ('2226','2003-08-16','09:41:00')
INSERT INTO @TempTable VALUES ('2227','2003-08-18','18:52:10') SELECT * FROM @TempTable
WHERE CONVERT(DATETIME,riqi+' '+shijian)<'2003-08-16 08:00:00'结果:
hm riqi shijian
2221 2003-08-15 18:20:18
2223 2003-08-13 18:55:56
2225 2003-08-15 18:23:21
union select * from hbiao where qiri='2003-08-16' and shijian<'08:00:00'