Krish Krish - 3 months ago 8
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

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)