日期:2014-05-18  浏览次数:20630 次

求一关于日期的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语句,偶写的太复杂了..-_-!