Krish Krish - 1 year ago 47
SQL Question

Get based on only month and year from table using mysql store producer but apply date parameter?

Declare
@date1 date = '2015-12-01'
,@date2 date = '2015-12-30'


BEGIN

Declare @date_u char = Month(@date1)
,@date_v char = Month(@date2)

Select
STD.StoreNo As StoreNo
,CheckDate as Date
,ProductBarCode as ProductBarCode
, SUM( StocktakingQty)AS ProducQty

From StockTakingDetail STD
Inner Join
(Select StoreNo,CheckNo,CheckDate
From StockTakingMain SM )StocktakingMain
On STD.CheckNo =StockTakingMain.CheckNo
where year(CheckDate) between @date_u and @date_v
group By STD.StoreNo,STD.ProductBarCode,CheckDate
End


actually i wanna show daily inventory ,but once a month only they use to take inventory in the warehouse, i cant able to fill dates gap without the data so i planned to use month and year only to get data !! but i cant able to find a way how to take input parameter only month and year from date!!

is anyone can help it!!

Answer Source

Do you want your where clause to look like this?

where year(CheckDate) * 100 + month(CheckDate) between year(@date1) * 100 + month(@date1) and
                                                       year(@date2) * 100 + year(@date2)

If so, I would really go fo:

where CheckDate >= date_sub(@date1, interval 1 - day(@date1)) and
      CheckDate < date_add(date_sub(@date2, interval 1 - day(@date2)), interval 1 month)