I have a below query which have a date filter like EST_PICK_DATE between '2015-02-01' and '2015-06-01', where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. I.E for current month MAY, EST_PICK_DATE should be between '2015-02-01' and '2015-06-01'.
I need to write below query dynamically.
In below query i have hardcoded the values '2015-02-01' and '2015-06-01', but it should take dynamically.
How to achieve this? I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price)
SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price,
FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID
WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
Try this
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price)
SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price,
FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID
WHERE ord_l.EST_PICK_DATE BETWEEN DATEADD(m, DATEDIFF(m, 0,DATEADD(month,-3,getdate())), 0) AND DATEADD(m, DATEDIFF(m, 0,DATEADD(month,1,getdate())), 0)
here are your start date and end date function
SELECT DATEADD(m, DATEDIFF(m, 0,DATEADD(month,-3,getdate())), 0)
2015-02-01 00:00:00.000
SELECT DATEADD(m, DATEDIFF(m, 0,DATEADD(month,1,getdate())), 0)
2015-06-01 00:00:00.000
For Specific date :
Declare @YourDate as date = '6/3/2015' -- note the format is mm/dd/yyyy
SELECT CAST(DATEADD(m, DATEDIFF(m, 0,DATEADD(month,-3,@YourDate)), 0) as date)
SELECT CAST(DATEADD(m, DATEDIFF(m, 0,DATEADD(month,1,@YourDate)), 0) as date)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments