关掉光猫远程控制:sql 时间比较 2006-01-01，20050101

select * from table_name where trunc(日期)＝to_date('2003-05-02','yyyy-mm-dd');
上面的语句可以查询字段“日期”中内容为20030502的数据。

select * from table_name where trunc(日期)＝to_date('20030502','yyyymmdd');
上面的语句可以查询字段“日期”中内容为2003-05-02的数据。

可通过自定义函数来解决

日期转化成"yyyymmdd"格式
CREATE Function DateString(@oDate as DateTime) RETURNS varchar(10) AS
begin
DECLARE @Year as varchar(4)
DECLARE @Month as varchar(4)
DECLARE @day as varchar(4)
set @Year = right('0000'+convert(varchar(4),datepart(year,@oDate)),4)
set @Month = right('00'+convert(varchar(2),datepart(Month,@oDate)),2)
set @Day = right('00'+convert(varchar(2),datepart(Day,@oDate)),2)
RETURN @Year+@Month+@Day
end

"yyyymmdd"转换成日期格式
CREATE Function StrDate(@Str as varchar(10))
RETURNS DateTime AS
begin
DECLARE @Date as datetime
DECLARE @Year as varchar(4)
DECLARE @Month as varchar(4)
DECLARE @day as varchar(4)
if (len(@Str) = 8 or len(@Str) = 6)
begin
if len(@Str) = 8
begin
set @Year=left(@Str,4)
set @month=substring(@Str,5,2)
end
else
begin
set @Year='20'+left(@Str,2)
set @month=substring(@Str,3,2)
end
set @day=right(@Str,2)
if (@Year>='1900' and @Year<'2100' and @Month>='01' and @Month<='12' and @day>='01' and @day<='31')
set @Date = convert(datetime,@Year+'-'+@Month+'-'+@Day)
end
RETURN @Date
end

函数建立后即可,如:
select dbo.DateString('2005-1-1'), dbo.StrDate('20050101')

如果只考虑转换的是日期类型的数据转换成'yyyymmdd'则只需要用 convert(char(8),datetime,112) 就可以

那可把2006-01-01转化为20060101
若time是2006-01-01
则convert(char(8),time,112)='20060101'

参考MSSQL帮助convert

你用的什么编程?

是要用什语言啊?