存储里调用函数
注:SUBSTRING(CAST(GETDATE() AS NVarChar(500)), 7, 4) 也是得到2007
SELECT SUBSTRING(CAST(GETDATE() AS NVarChar(500)), 6, 5) AS Expr1 FROM jdkh
SELECT SUBSTRING(CAST(GETDATE() AS NVarChar(500)), 7, 4) AS Expr1 FROM jdkh
得到的都是2007
SELECT GETDATE() AS Expr1 FROM jdkh
得到的是2007-3-2 10:11:33
-----------------------------------------
为什么存储里
set @strSql4= @strSql4+ ' ' + 'and q11 = (SUBSTRING(CAST(GETDATE() AS NVarChar(500)), 6, 5))'
找不出数据
就是要
set @strSql4= @strSql4+ ' ' + 'and q11 = (SUBSTRING(CAST(GETDATE() AS NVarChar(500)), 7, 4))'
-------------------------------------------
函数写在哪里?
Tables 建立表
Stored Procedures 建立存储
写在User Defined Functions
存储:
if @sql3 is null or @sql3=''
set @strSql4= @strSql4+ ' ' + 'and (substring(cast(q10 as NVarChar(500)),6,5))=(substring(cast(getdate() as NVarChar(500)),6,5))'
else
begin
set @strSql4= @strSql4+ ' ' + 'and (substring(cast(q10 as NVarChar(500)),6,5))='+@sql3+''
end
查找和某年相等的数据或本年的数据
if @sql4 is null or @sql4=''
set @strsql4 = @strSql4+ ' ' + ' and dbo.ComputeQuarter(q10) = dbo.ComputeQuarter2(getdate())'
else
begin
set @strsql4 = @strSql4+ ' ' + ' and dbo.ComputeQuarter(q10) = '+ @sql4
end
q10和getdate()都是参数
第一个函数dbo.ComputeQuarter(getdate())功能是显示本季度的数据
第二个函数dbo.ComputeQuarter2(q10)功能是显示和某日期相对应季度的数据(判断日期属于哪个季度)
dbo.ComputeQuarter(q10)就是把时间字段q10转化为季度的函数,等价于datepart(qq,q10)
-------------------------
函数1:
CREATE FUNCTION dbo.ComputeQuarter (@DATE datetime)
RETURNS int
AS
BEGIN
declare @month int
declare @jidu int
set @month = month(@DATE)
if(@month > 0 and @month <4)
set @jidu =1
else if(@month >= 4 and @month <7)
set @jidu =2
else if(@month >= 7 and @month <10)
set @jidu =3
else
set @jidu =4
RETURN(@jidu)
END
函数2:
CREATE FUNCTION dbo.ComputeQuarter2(@DATE datetime)
RETURNS int
AS
BEGIN
declare @month int
declare @jidu int
set @month = month(@DATE)
if(@month > 0 and @month <4)
set @jidu =1
else if(@month >= 4 and @month <7)
set @jidu =2
else if(@month >= 7 and @month <10)
set @jidu =3
else
set @jidu =4
RETURN(@jidu)
END