有一表doc,如下,scriqi是生产日期,yxriqi为有效期,现在有部分产品有有效期,但是没有生产日期,如何将产品的有效期提早两年转变为生产日期,有效期不变。例如,可乐的有效期为2013-05-04日,那生产日期就要为2011-05-04,柠檬茶原来有生产企业,不变。scriqi和yxriqi都为varchar(10)。ID spname scriqi yxriqi
1 可乐 2013-05-04
2 雪碧 2016-04-01
3 柠檬茶 2011-05-01 2014-02-02
4 果汁 2013-06-08
1 可乐 2013-05-04
2 雪碧 2016-04-01
3 柠檬茶 2011-05-01 2014-02-02
4 果汁 2013-06-08
update tb
set scriqi = dateadd(yy,-2,yxriqi)
这样会把原有生产日期的也改掉,但是如果我加条件scriqi<>'',会提示从 varchar 数据类型到 datetime 数据类型的转换产生一个超出范围的值。
update tb
set scriqi = dateadd(yy,-2,yxriqi)
where scriqi is null
提示后面的scriqi附近有语法错误。
set scriqi = dateadd(yy,-2,yxriqi)
where yxriqi is not null and scriqi is null
set scriqi = cast( dateadd(yy,-2,cast(yxriqi as datetime) as varchar(10))
where yxriqi is not null and scriqi is null
UPDATE doc
SET scriqi = CAST(dateadd(yy, -2, yxriqi) as varchar(10))
WHERE yxriqi IS NOT NULL AND scriqi IS NULL
很奇怪,我只要加上 scriqi IS NULL就查询不出结果,但是scriqi肯定是有空的。
少一括弧
if object_id('tempdb.dbo.#t') is not null drop table #t
create table #t (ID int,spname varchar(6),scriqi datetime,yxriqi datetime)
insert into #t
select 1,'可乐',null ,'2013-05-04' union all
select 2,'雪碧',null,'2016-04-01' union all
select 3,'柠檬茶','2011-05-01','2014-02-02' union all
select 4,'果汁',null,'2013-06-08'select * from #t
select id,spname, isnull(scriqi,DATEADD (yy,-2,yxriqi) )scriqi,yxriqi from #t
id spname scriqi yxriqi
1 可乐 2011-05-04 00:00:00.000 2013-05-04 00:00:00.000
2 雪碧 2014-04-01 00:00:00.000 2016-04-01 00:00:00.000
3 柠檬茶 2011-05-01 00:00:00.000 2014-02-02 00:00:00.000
4 果汁 2011-06-08 00:00:00.000 2013-06-08 00:00:00.000
insert into #t
select 1,'可乐',null ,'2013-05-04' union all
select 2,'雪碧',null,'2016-04-01' union all
select 3,'柠檬茶','2011-05-01','2014-02-02' union all
select 4,'果汁',null,'2013-06-08'update #t set scriqi=convert(varchar(10),dateadd(yy,-2,yxriqi),120) where scriqi is nullselect * from #t/*
ID spname scriqi yxriqi
----------- ------ ---------- ----------
1 可乐 2011-05-04 2013-05-04
2 雪碧 2014-04-01 2016-04-01
3 柠檬茶 2011-05-01 2014-02-02
4 果汁 2011-06-08 2013-06-08(所影响的行数为 4 行)
*/
同样,where scriqi is null,我的就找不到结果。
create table #t (ID int,spname varchar(6),scriqi varchar(10),yxriqi varchar(10))
insert into #t
select 1,'可乐',null ,'2013-05-04' union all
select 2,'雪碧',null,'2016-04-01' union all
select 3,'柠檬茶','2011-05-01','2014-02-02' union all
select 4,'果汁',null,'2013-06-08'update #t set scriqi=convert(varchar(10),dateadd(yy,-2,yxriqi),120)
where isnull(scriqi,'')=''select * from #t/*
ID spname scriqi yxriqi
----------- ------ ---------- ----------
1 可乐 2011-05-04 2013-05-04
2 雪碧 2014-04-01 2016-04-01
3 柠檬茶 2011-05-01 2014-02-02
4 果汁 2011-06-08 2013-06-08(所影响的行数为 4 行)
*/
create table #t (ID int,spname varchar(6),scriqi varchar(10),yxriqi varchar(10))
insert into #t
select 1,'可乐',null ,'2013-05-04' union all
select 2,'雪碧',null,'2016-04-01' union all
select 3,'柠檬茶','2011-05-01','2014-02-02' union all
select 4,'果汁',null,'2013-06-08'update #t set scriqi=convert(varchar(10),dateadd(yy,-2,yxriqi),120)
where isnull(scriqi,'')=''select * from #t/*
ID spname scriqi yxriqi
----------- ------ ---------- ----------
1 可乐 2011-05-04 2013-05-04
2 雪碧 2014-04-01 2016-04-01
3 柠檬茶 2011-05-01 2014-02-02
4 果汁 2011-06-08 2013-06-08(所影响的行数为 4 行)
*/
语句已终止。