0

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?

4
  • 3
    Do you have a calendar table ? This is an utility table , that you can create in your system. mssqltips.com/sqlservertip/4054/…
    – Sabin B
    Mar 13, 2019 at 10:04
  • I don't have calendar table.
    – midtonight
    Mar 13, 2019 at 10:08
  • You could create this calendar table or you could use ,for example for row 31 DATEADD(DAY,31,@StartDate) instead of CONVERT... like so (DocEntry,DATEADD(DAY,31,@StartDate),U_Delivery_31) this will be out of desired range and will not be selected
    – Sabin B
    Mar 13, 2019 at 10:43
  • Please don't cast to varchar without specifying length (see this post). Also, can you show the structure of your @SALESORDERFORECASTH and @SALESORDERFORECASTD tables? Mar 13, 2019 at 12:49

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.