分解日期之间的日期，检查和调整参数 [英] Explode Dates Between Dates, check and adjust parameter

问题描述

我使用了 Get两个日期之间的日期列表，使用一个功能非常出色的函数.

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

However, I need to enter an additional check on the enddate that is passed. Sometimes this date will be null because if the record is still current, it will not have been entered. If it is, I would like to substitute it with the current date, so that the dates are returned from the start date to the current date.

我想输入的代码是这样的，但是不管我把它放在哪里，都会出现语法错误.

The code I want to enter is this, but wherever I try and put it, there are syntax errors.

 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 值.

You need to add 1 more CTE to filter the @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
);

这篇关于分解日期之间的日期，检查和调整参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！