需要if语句来检查空UDF SQL server

Question

我有一个函数来获取服务长度，我需要执行一个if语句来检查空日期，我是否能够使用术语if@dateleftorg=='1900-01-01 00:00:00.000'我需要做的是，如果@dateleftorg为空，则使用GETDATE()，否则我使用@dateleftorg中的值

ALTER FUNCTION [dbo].[hmsGetLosText]
    (@FromDt as datetime,@DateLeftOrg as Datetime) 
returns varchar(255)
as
BEGIN
    DECLARE @yy AS SMALLINT, @mm AS INT, @dd AS INT,
            @getmm as INT, @getdd as INT, @Fvalue varchar(255)


    SET @yy = DATEDIFF(yy, @FromDt, GETDATE())
    SET @mm = DATEDIFF(mm, @FromDt, GETDATE())
    SET @dd = DATEDIFF(dd, @FromDt, GETDATE())
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @FromDt), GETDATE()))
    SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @FromDt), GETDATE()), DATEADD(yy, @yy, @FromDt)), GETDATE()))
    IF @getmm = 1
    set @getmm=0

    RETURN (
      Convert(varchar(10),@yy) + 'y ' + Convert(varchar(10),@getmm) + 'm ')
END

Answer 1

您可以使用IsNull或coalesce。像这样：

ALTER FUNCTION [dbo].[hmsGetLosText]
   (@FromDt as datetime,@DateLeftOrg as Datetime) 
returns varchar(255)
AS
BEGIN
    DECLARE @yy AS SMALLINT, @mm AS INT, @dd AS INT,
        @getmm as INT, @getdd as INT, @Fvalue varchar(255)

    --if @DateLeftOrg is null, use GetDate
    SET @DateLeftOrg = CASE WHEN @DateLeftOrg IS NULL THEN GetDate()
       WHEN @DateLeftOrg = '1/1/1900' THEN GetDate()
       ELSE @DateLeftOrg
       END

    SET @yy = DATEDIFF(yy, @FromDt, @DateLeftOrg)
    SET @mm = DATEDIFF(mm, @FromDt, @DateLeftOrg)
    SET @dd = DATEDIFF(dd, @FromDt, @DateLeftOrg)
    SET @getmm = ABS(DATEDIFF(mm, DATEADD(yy, @yy, @FromDt), @DateLeftOrg))
    SET @getdd = ABS(DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, DATEADD(yy, @yy, @FromDt), @DateLeftOrg)), DATEADD(yy, @yy, @FromDt)), @DateLeftOrg))
    IF @getmm = 1
       set @getmm=0

    RETURN (
      Convert(varchar(10),@yy) + 'y ' + Convert(varchar(10),@getmm) + 'm ')
END