Click here to Skip to main content
15,745,973 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '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'
Updated 25-May-15 1:36am

use the DateAdd[^] function in where condition
Share this answer
Logic is here: SQL SERVER – Query to Find First and Last Day of Current Month[^]

Check this:
DECLARE @startdate DATE = GETDATE() --now!
SET @startdate = DATEADD(MM, -3, DATEADD(DD, -DAY(@startdate)+1, @startdate))
DECLARE @enddate DATE = DATEADD(MM, 4, @startdate)

SELECT CONVERT(DATETIME, @startdate) AS StartDate, CONVERT(DATETIME, @enddate) AS EndDate

Above query returns:
StartDate				EndDate
2015-02-01 00:00:00.000	2015-06-01 00:00:00.000

In your case, WHERE statement must be changed as follow:
WHERE ord_l.EST_PICK_DATE BETWEEN @startdate AND @enddate
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900