select * from tb where cast(ltrim(年)+'-'+ltrim(月)+'-'+ltrim(日)as datetime) between '2008-02-03' and '2009-12-14'
--如果年月日字段为字符型 select * from tb where cast(年+'-'+月+'-'+日 as datetime)>='2008-2-3' and cast(年+'-'+月+'-'+日 as datetime)<='2009-12-14'
DECLARE @T TABLE ( id INT IDENTITY(1, 1) , [Y] VARCHAR(10) , [M] VARCHAR(10) , [D] VARCHAR(10) )INSERT INTO @T([Y],[M],[D]) SELECT '2008','9','10' UNION ALL SELECT '2008','2','1' UNION ALL SELECT '2010','1','1' UNION ALL SELECT '2009','10','8' SELECT * FROM @t WHERE [y]+'年'+[M]+'月'+[D]+'日' BETWEEN '2008年2月3日' AND '2009年12月14日'/*(4 行受影响) id Y M D ----------- ---------- ---------- ---------- 1 2008 9 10 4 2009 10 8(2 行受影响) */
SELECT CDate(CStr(Year)+'-'+CStr(Month)+'-'+CStr(Day)) FROM test where CDate(CStr(Year)+'-'+CStr(Month)+'-'+CStr(Day)) between CDate('2008-2-3') and CDate('2009-12-14')
SELECT CDate(CStr(Year)+'-'+CStr(Month)+'-'+CStr(Day)) FROM test where CDate(CStr(Year)+'-'+CStr(Month)+'-'+CStr(Day)) between CDate('2008年2月3日') and CDate('2009年12月14日')
--如果年月日字段为字符型 select * from tb where cast(年+'-'+月+'-'+日 as datetime)>='2008-2-3' and cast(年+'-'+月+'-'+日 as datetime)<='2009-12-14'
from tb
where cast(ltrim(年)+'-'+ltrim(月)+'-'+ltrim(日)as datetime)
between '2008-02-03' and '2009-12-14'
--如果年月日字段为字符型
select *
from tb
where cast(年+'-'+月+'-'+日 as datetime)>='2008-2-3'
and cast(年+'-'+月+'-'+日 as datetime)<='2009-12-14'
(
id INT IDENTITY(1, 1) ,
[Y] VARCHAR(10) ,
[M] VARCHAR(10) ,
[D] VARCHAR(10)
)INSERT INTO @T([Y],[M],[D])
SELECT '2008','9','10' UNION ALL
SELECT '2008','2','1' UNION ALL
SELECT '2010','1','1' UNION ALL
SELECT '2009','10','8' SELECT * FROM @t WHERE [y]+'年'+[M]+'月'+[D]+'日' BETWEEN '2008年2月3日' AND '2009年12月14日'/*(4 行受影响)
id Y M D
----------- ---------- ---------- ----------
1 2008 9 10
4 2009 10 8(2 行受影响)
*/
FROM test
where CDate(CStr(Year)+'-'+CStr(Month)+'-'+CStr(Day)) between CDate('2008-2-3') and CDate('2009-12-14')
FROM test
where CDate(CStr(Year)+'-'+CStr(Month)+'-'+CStr(Day)) between CDate('2008年2月3日') and CDate('2009年12月14日')
select *
from tb
where cast(年+'-'+月+'-'+日 as datetime)>='2008-2-3'
and cast(年+'-'+月+'-'+日 as datetime)<='2009-12-14'