select datediff(dd,cast((cast(year('2005-5-2') as varchar)+'-'+cast(month('2005-5-2') as varchar)+'-1') as datetime),
cast((cast(year('2005-5-2') as varchar)+'-'+cast(month('2005-5-2')+1 as varchar)+'-1') as datetime))
cast((cast(year('2005-5-2') as varchar)+'-'+cast(month('2005-5-2')+1 as varchar)+'-1') as datetime))
datediff(dd,cast((cast(year('2005-12-2') as varchar)+'-'+cast(month('2005-12-2') as varchar)+'-1') as datetime),cast((cast(year('2005-12-2')+1 as varchar)+'-'+cast(1 as varchar)+'-1') as datetime))
else
datediff(dd,cast((cast(year('2005-12-2') as varchar)+'-'+cast(month('2005-12-2') as varchar)+'-1') as datetime),cast((cast(year('2005-12-2') as varchar)+'-'+cast(month('2005-12-2')+1 as varchar)+'-1') as datetime))
end
set @ny='200511'select case when cast(right(@ny,2) as int)=12 then
datediff(dd,cast((left(@ny,4)+'-'+cast(cast(right(@ny,2) as int) as varchar)+'-1') as datetime), cast(cast(cast(left(@ny,4) as int)+1 as varchar)+'-1'+'-1' as datetime))
else
datediff(dd,cast((left(@ny,4)+'-'+cast(cast(right(@ny,2) as int) as varchar)+'-1') as datetime), cast((left(@ny,4)+'-'+cast(cast(right(@ny,2) as int)+1 as varchar)+'-1') as datetime))
end as 当月天数
AS
SELECT NY AS ny, case when cast(right(@ny,2) as int)=12 then
datediff(dd,cast((left(@ny,4)+'-'+cast(cast(right(@ny,2) as int) as varchar)+'-1') as datetime), cast(cast(cast(left(@ny,4) as int)+1 as varchar)+'-1'+'-1' as datetime))
else
datediff(dd,cast((left(@ny,4)+'-'+cast(cast(right(@ny,2) as int) as varchar)+'-1') as datetime), cast((left(@ny,4)+'-'+cast(cast(right(@ny,2) as int)+1 as varchar)+'-1') as datetime))
end as 当月天数,
d1/当月天数 AS d1pingjun FROM t1提示t1中没有当月天数这个字段,我如何在创建视图的时候直接引用呢?
select (case when month(getdate())in(1,3,5,7,8,10,12) then 31
when month(getdate())=2 then datediff(day,cast(year(getdate())as varchar)+'-02-1',cast(year(getdate())as varchar)+'-03-1')
else 30 end) as 当月天数