sql server

1.处理时间 yyyy-MM-dd

select format(SYSDATETIME(),'yyyy-MM-dd')

View Code

2.获取入职10天的所有用户

 select * from sys_user where DATEDIFF(day,onboardTime,GETDATE())=10

View Code

 3.声明 调用的函数

create function dbo.func_get_score(@UserCode nvarchar(500))      --（@参数名 参数的数据类型）
returns  int   --返回返回值的数据类型,   --[WITH ENCRYPTION]     --如果指定了 encryption 则函数被加密

as
begin
    declare @score int       -- 声明返回变量    

    select @score = sum(tmp.cnt) from (
            -- 查询体
            select t.Dcnt*1 cnt from (
                        select count(1) Dcnt, 
                                        UserCode 
                                from [dbo].[BS_Degree] as de 
                                inner join [dbo].[v_FullMenu] as fu on de.Content_ID = fu.ID where de.CreateDate<CONVERT(datetime, '2021-08-09 23:59:59') group by UserCode 
            )t where t.UserCode=@UserCode   

            union all
            select t.Dcnt*1 cnt from (
                        select count(1) Dcnt,
                                        UserCode 
                                from [dbo].[Manager_Degree] where CreateDate<CONVERT(datetime, '2021-08-09 23:59:59') group by UserCode
            )t where t.UserCode=@UserCode
        )tmp
    return  @score
end
GO

View Code