Sql-server – Using IF clause or while clause in OUTER APPLY

sql serversql-server-2008sql-server-2008-r2

I want to select dates between @startdate and @enddate but I get an error when a month doesn't have 31 days.

DECLARE @StartDate date, @EndDate date
set @StartDate = '2019-04-01'
set @EndDate = '2019-04-30'

(select T1.dday 
from [@SALESORDERFORECASTH] T0 
    INNER JOIN (SELECT t.*
FROM [@SALESORDERFORECASTD] 
OUTER APPLY 
(
    VALUES 
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120) 
        +cast('-01' as varchar)),120),U_Delivery_1),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120) 
        +cast('-02' as varchar)),120),U_Delivery_2),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120) 
        +cast('-03' as varchar)),120),U_Delivery_3),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120) 
        +cast('-04' as varchar)),120),U_Delivery_4),
        .
        .
        .
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120) 
        +cast('-30' as varchar)),120),U_Delivery_30),
        (DocEntry,CONVERT(Datetime,(CONVERT(VARCHAR(7), @StartDate, 120) 
        +cast('-31' as varchar)),120),U_Delivery_31)
    ) t (DocEntry,dday,U_Delivery)) T1 ON T0.DocEntry = T1.DocEntry 
    AND T1.dday <= @EndDate
group by T1.dday)

In this code I want to select T1.dday between 2019-04-01 to 2019-04-30 but in VALUE have 31 days. Leads to this error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Can I use If or while or when clause to check date in this VALUES?

Best Answer

I don't quite understand your table structure or your query, but a much more sane way to generate a small date range (< 100 days) between a start and end date - given that you don't have a calendar table (even though you should, and shouldn't easily be able to argue against one) - is as follows:

DECLARE @StartDate date = '20190401', 
        @EndDate   date = '20190430';

;WITH days(d) AS
(
  SELECT @StartDate
  UNION ALL
  SELECT DATEADD(DAY,1,d) FROM days WHERE d < @EndDate
)
SELECT d FROM days;

If your range can be more than 100 days, you can use OPTION (MAXRECURSION n).

Now you can use that CTE to anchor / join / apply etc. to the rest of your query. I can help do that but you'll need to show table structure, sample data, and desired results. Basically, you need to provide an MCVE if you want quality help - this takes out guesswork, minimizes back-and-forth, and helps identify edge cases you may not have considered yet.

Related Question