How can code User input parameter from first date to last date (SQL query manager)

Question

I need help, I write the code and have to use The Last Date from user parameter >> [%1] in statement but when I execute ..the filled window force me fill from the last date to first date. It should be fill from the first date to last date.

when I execute... the "User window" popup and need to input date from the first date to last date (ex: 01/08/20 - 30/09/20) but from the fact from above coding,

user have to input the last date to the first date (ex: 30/09/20 - 01/08/20) << this will make user confuse
I attached the pic for you can see it clear>>

Select 
    T0.docnum , T0.docdate , Sum(T1.Quantity), 
    Sum(case when T0.docdate between dateadd(month,0,DATEADD(DAY,1-DATEPART(day,'[%1]'),'[%1]')) and eomonth(dateadd(month,0,DATEADD(DAY,1-DATEPART(day,'[%1]'),'[%1]'))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 'm12_Lts'  ,
    sum(case when T0.docdate between dateadd(month,-1,DATEADD(DAY,1- 
    DATEPART(day,'[%1]'),'[%1]')) and eomonth(dateadd(month,-1,DATEADD(DAY,1- 
    DATEPART(day,'[%1]'),'[%1]'))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 'm11_Lts'
From    OINV T0 
    left join inv1 T1 on T0.DocEntry = T1.DocEntry 
    left join oitm T2 on T1.ItemCode = T2.ItemCode 
Where 
    T0.docdate >= '[%0]' and T0.Docdate <= '[%1]' 
Group By 
    T0.docnum, T0.Docdate

Answer 1

Try Inserting

/*SELECT FROM [dbo].[OWTR] p1*/

declare @FROM as Datetime
declare @TO as datetime
/* WHERE */
set @FROM = /* p1.docdate */ '[%0]'
set @TO = /* p1.docdate */  '[%1]'

at the top of your query (the table doesn't really matter) then replace the [%0] with @FROM and [%1] with @TO in the rest of your query.

Full Code:

/*SELECT FROM [dbo].[OWTR] p1*/

 declare @FROM as Datetime
 declare @TO as datetime
 /* WHERE */
 set @FROM = /* p1.docdate */ '[%0]'
 set @TO = /* p1.docdate */  '[%1]'



Select 
T0.docnum , T0.docdate , Sum(T1.Quantity), 
Sum(case when T0.docdate between dateadd(month,0,DATEADD(DAY,1- 
DATEPART(day,@TO),@TO)) and eomonth(dateadd(month,0,DATEADD(DAY,1- 
 DATEPART(day,@TO),@TO))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 
'm12_Lts'  ,
sum(case when T0.docdate between dateadd(month,-1,DATEADD(DAY,1- 
DATEPART(day,@TO),@TO)) and eomonth(dateadd(month,-1,DATEADD(DAY,1- 
DATEPART(day,@TO),@TO))) then T1.Quantity*T2.U_hmc_vollts else 0 end) as 
'm11_Lts'
From    OINV T0 
left join inv1 T1 on T0.DocEntry = T1.DocEntry 
left join oitm T2 on T1.ItemCode = T2.ItemCode 
Where 
T0.docdate >= @FROM and T0.Docdate <= @TO 
Group By 
T0.docnum, T0.Docdate

let me know how you get on.

Answer 2

Add order by to your SQL statement.

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-order-by-clause-transact-sql?view=sql-server-ver15