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?
calendar table
? This is an utility table , that you can create in your system. mssqltips.com/sqlservertip/4054/…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 selectedvarchar
without specifying length (see this post). Also, can you show the structure of your@SALESORDERFORECASTH
and@SALESORDERFORECASTD
tables?