日期校验(sql case when实现)
作者/cadenza7
因为数据库里表的字段没有设置约束,以至于存储的数据不规范,而此时提取数据时有如下要求:
a、b、c为TX表中的字段,数据类型均为DateTime,且无空值(NULL)。
a不能小于2003-01-01,b不能小于2006-01-01且不能小于a,c不能小于b。
必须用SQL实现,不允许将数据提取到数据集(如DataGrid)进行操作。
declare @a datetime
declare @b datetime
declare @c datetime
--a(a>='2003-01-01')
set @a='2000-01-01'
--set @a='2007-01-01'
select (case when @a<'2003-01-01' then '2003-01-01' else @a end)a
--b((b>='2006-01-01') and (b>=a))
--b1
set @a='2009-01-01'set @b='2007-01-01'
--set @a='2005-01-01'set @b='2003-01-01'
--set @a='2007-01-01'set @b='2003-01-01'
select (case when @b<'2006-01-01' then '2006-01-01' else @b end)b
select (case when @b<@a then @a else @b end)b
--b2
set @a='2005-01-01'set @b='2003-01-01'
--set @a='2009-01-01'set @b='2007-01-01'
--set @a='2006-01-01'set @b='2005-01-01'
select (case when (case when @b<'2006-01-01' then '2006-01-01' else @b end)<@a then @a else @b end)b
select (case when (case when @b<@a then @a else @b end)<'2006-01-01' then '2006-01-01' else @b end)b
--b3
select (case when (case when @b<'2006-01-01' then '2006-01-01' else @b end)<@a then @a else (case when @b<'2006-01-01' then '2006-01-01' else @b end) end)b
select (case when (case when @b<@a then @a else @b end)<'2006-01-01' then '2006-01-01' else (case when @b<@a then @a else @b end) end)b
--c(c>=b)
set @a='2002-01-01'set @b='2000-01-01'set @c='2001-01-01'
set @a='2007-01-01'set @b='2008-01-01'set @c='2004-01-01'
--select (case when @c<@b then @b else @c end)c
--把b3代入上面表达式中(替换@b)
select (case when
@c<(case when (case when @b<'2006-01-01' then '2006-01-01' else @b end)<@a then @a else (case when @b<'2006-01-01' then '2006-01-01' else @b end) end)
then
(case when (case when @b<'2006-01-01' then '2006-01-01' else @b end)<@a then @a else (case when @b<'2006-01-01' then '2006-01-01' else @b end) end)
else @c end)c
版权所有,转载请务必标明文章出处或保留以下信息!谢谢!!
作者主页 http://blog.csdn.net/cadenza7