Sql server 日期型与数值型的转换....
---问题---
/*
现在遇到一个问题: 数据库是sql server2000,前台录入的是时间,但是数据库中存的确是int型,
如果我要按照日期查找(例如找2007-3-12到2007-3-24的数据),我应该如何写sql语句. 假设表名为test,
保存日期的字段名为open_date.
*/
declare
@test table
(open_date int)
declare @i_date datetime
set @i_date='2007-02-01'
insert into @test select convert(int,@i_date)i_date
set @i_date='2007-03-01'
insert into @test select convert(int,@i_date)i_date
set @i_date='2007-03-12'
insert into @test select convert(int,@i_date)i_date
set @i_date='2007-03-13'
insert into @test select convert(int,@i_date)i_date
select * from @test
select open_date, convert(datetime,open_date)as new_date from @test
where convert(datetime,open_date)>='2007-3-12'
and convert(datetime,open_date)<='2007-3-24'
---运行结果--
open_date
-----------
39112
39140
39151
39152
(4 row(s) affected)
open_date new_date
----------- ------------------------------------------------------
39151 2007-03-12 00:00:00.000
39152 2007-03-13 00:00:00.000
(2 row(s) affected)