分解日期之间的日期，检查和调整参数

我使用了一个函数使用函数获取两个日期之间的日期列表效果非常好。

但是，我需要对通过的结束日期进行额外检查。有时此日期将为空，因为如果记录仍然是最新的，则不会输入该记录。如果是，我想用当前日期替换它，以便返回从开始日期到当前日期的日期。

我想输入的代码是这样的，但是无论我尝试输入它，都会出现语法错误。

 IF @endddate IS NULL
     SET @enddate = getdate()

这是现在的代码

 CREATE FUNCTION [dbo].[ExplodeDates](@startdate datetime, @enddate datetime) 
 returns table as 
 return ( 
 with  
 N0 as (SELECT 1 as n UNION ALL SELECT 1) 
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2) 
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2) 
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2) 
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2) 
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2) 
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2) 
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6) 
SELECT DATEADD(day,num-1,@startdate) as thedate 
FROM nums 
WHERE num <= DATEDIFF(day,@startdate,@enddate) + 1 
 );

任何帮助将不胜感激。

您还需要添加1个CTE来过滤@enddate value.

尝试这个 ： -

CREATE FUNCTION dbo.ExplodeDates(@startdate datetime, @enddate datetime)
returns table as
return (
with 
 N0 as (SELECT 1 as n UNION ALL SELECT 1)
,N1 as (SELECT 1 as n FROM N0 t1, N0 t2)
,N2 as (SELECT 1 as n FROM N1 t1, N1 t2)
,N3 as (SELECT 1 as n FROM N2 t1, N2 t2)
,N4 as (SELECT 1 as n FROM N3 t1, N3 t2)
,N5 as (SELECT 1 as n FROM N4 t1, N4 t2)
,N6 as (SELECT 1 as n FROM N5 t1, N5 t2)
,nums as (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) as num FROM N6)
,checkDate as (select case when
                          @enddate is null then getdate() 
                          else @enddate
                      end as  dt)
SELECT DATEADD(day,num-1,@startdate) as thedate
FROM nums
WHERE num <= DATEDIFF(day,@startdate,(Select dt from checkDate)) + 1
);