1

I have used a function from Get a list of dates between two dates using a function which works brilliantly.

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()

Here is the Code as it stands

 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 
 );

Any help would be greatly appreciated.

Community
  • 1
  • 1
StripyTiger
  • 877
  • 1
  • 14
  • 31

1 Answers1

2

You need to add 1 more CTE to filter the @enddate value.

Try this : -

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
);
praveen
  • 12,083
  • 1
  • 41
  • 49