需要为Date逻辑编写查询 [英] Need to write query for Date logic

问题描述

我有一个下面的查询，其日期过滤器类似于2015-02-01'和'2015-06-01'之间的EST_PICK_DATE，其中逻辑EST_PICK_DATE应该是当月和第一个日期的3个月下个月。本月的Ex，EST_PICK_DATE应介于'2015-02-01'和'2015-06-01'之间。我需要动态地写下面的查询。在下面的查询中，我已对该值进行了硬编码（2015-02-01和2015-06-01之间的EST_PICK_DATE），但它应该是动态的。如何实现这个？

我在SSIS包中使用这个查询，那么我应该在SQL级别做什么，或者我们应该在包中实现这个逻辑？如果是，如何？

  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' 

解决方案

使用DateAdd [< a href =http://www.w3schools.com/sql/func_dateadd.asptarget =_ blanktitle =新窗口> ^ ]函数在where where

逻辑在这里： SQL SERVER - 查询当前月份的第一天和最后一天 [ ^ ]



选中：

  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 ，< span class =code-sdkkeyword> @ startdate ） AS  StartDate， CONVERT （< span class =code-keyword> DATETIME ， @ enddate ） AS  EndDate 

以上查询返回：

  StartDate EndDate  
 2015-02- 01 00：00：00.000 2015-06-01 00：00：00.000 

在您的情况下， WHERE 语句必须更改如下：

  WHERE  ord_l.EST_PICK_DATE  BETWEEN   @startdate   AND   @EndDate  

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'

解决方案

use the DateAdd[^] function in where condition

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

这篇关于需要为Date逻辑编写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！