求一关于日期的SQL语句
一表中有两字段iodate,iotime
iodate iotime
------------------------
2006-07-27 12:11:10
2006-07-27 12:10:00
2006-10-01 11:10:10
2006-10-01 11:11:00
2006-11-02 10:05:20
2006-11-02 10:10:00
查出在06年7月27日 12:11:10 到 06年11月2日 10:10:00 之间的数据
求一条简洁的SQL语句,偶写的太复杂了..-_-!
[解决办法]
select * from tablename where '2006-07-27 ' <iodate < '2006-11-02 '
union all
select * from tablename where iotime> = '12:11:10 ' and iodate= '2006-07-27 '
union all
select * from tablename where iotiem <= '10:10:00 ' and iodate= '2006-11-02 '
[解决办法]
select * from tablename
where cast(iodate + ' ' + iotime as datetime)
between '2006-07-27 12:11:10 'and '2006-11-02 10:10:00 '
[解决办法]
select CAST(DATEPART(YEAR,x) AS VARCHAR)+ '- ' + CAST(DATEPART(MONTH,x) AS VARCHAR) + '- ' + CAST(DATEPART(DAY,x) AS VARCHAR) iodate, CAST(DATEPART(HOUR,x) AS VARCHAR) + ': ' + CAST(DATEPART(MINUTE,x) AS VARCHAR) + ': ' + CAST(DATEPART(SECOND,x) AS VARCHAR) iotime from (select iodate + ' ' + iotime x from bs) b
where b.x between '2006-12-27 12:10:10 ' and '2006-12-29 10:08:10 '
[解决办法]
--try
select * from tbName where cast(iodate+ ' '+iotime as datetime) between '2006-7-27 12:11:10 ' and '2006-11-2 10:10:00 '
[解决办法]
create table T(iodate varchar(10),iotime varchar(8))
insert into T
select '2006-07-27 ', '12:11:10 ' union all
select '2006-07-27 ', '12:10:00 ' union all
select '2006-10-01 ', '11:10:10 ' union all
select '2006-10-01 ', '11:11:00 ' union all
select '2006-11-02 ', '10:05:20 ' union all
select '2006-11-02 ', '10:10:00 '
select * from T
where convert(datetime,iodate+ ' ' + iotime) between '2006-7-27 12:11:10 ' and '2006-11-2 10:10:00 '
drop table T
--查出在06年7月27日 12:11:10 到 06年11月2日 10:10:00 之间的数据
--求一条简洁的SQL语句,偶写的太复杂了..-_-!
