April 10, 2012 at 12:33 pm
I can't get the Convert or Cast to work please help
DATEADD(month,-3,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0)))
for use in a between statment like
DEMAND_DATE BETWEEN DATEADD(month,-2,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0)))
AND DATEADD(month,-2,DATEADD(DD, 1 - DAY(GETDATE()), GETDATE()))
April 10, 2012 at 12:35 pm
twdavis-893252 (4/10/2012)
I can't get the Convert or Cast to work please help
DATEADD(month,-3,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0)))for use in a between statment like
DEMAND_DATE BETWEEN DATEADD(month,-2,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0)))AND DATEADD(month,-2,DATEADD(DD, 1 - DAY(GETDATE()), GETDATE()))
1) What dates are you trying to calculate?
2) You shouldn't use between, you should use >= @StartDate and < @EndDate.
April 10, 2012 at 12:35 pm
What are you trying to do? You did not give us any insight into what your CAST/CONVERT should do, neither did u say what your query's intentions is. More details pls.
April 10, 2012 at 12:39 pm
Looking at the code you did post, you'd get nothing in return any way. It looks like the first date is > the second date. Using BETWEEN the first value must be <= to the second value.
April 10, 2012 at 12:44 pm
Looking at the dates you tried to calculate, I'd say you are looking for the start and end for 2 months ago.
Here is some code to help you:
declare @ThisDate datetime,
@StartDate datetime,
@EndDate datetime;
set @ThisDate = getdate();
select @StartDate = dateadd(mm, datediff(mm, 0, @ThisDate) - 2, 0), @EndDate = dateadd(mm, datediff(mm, 0, @ThisDate) - 1, 0);
select @StartDate StartDate, @EndDate EndDate;
For some common date routines: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
April 10, 2012 at 12:51 pm
This is my whole statement the convert I use gets me an error of
"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value"
SELECT MP.EXT_EMAIL_ADDR,TC.DESCR,OH.SUPPORT_TEAM_CD,IND.CUST_ID,IND.CUST_NAME,IM.INV_PROD_FAM_CD, IIF.DESCR AS FAMILY,
ISNULL((SELECT SUM(IND2.QTY_REQUESTED - IND2.QTY_BACKORDER) FROM
PS_IN_DEMAND IND2, PS_MASTER_ITEM_TBL IM2, PS_ORD_HEADER OH2
WHERE IND2.INV_ITEM_ID = IM2.INV_ITEM_ID
AND IND2.ORDER_NO = OH2.ORDER_NO
AND IM2.DESCR60 LIKE '%DRESSER%'
AND IM2.INV_PROD_FAM_CD = IM.INV_PROD_FAM_CD
AND OH2.CUSTOMER_PO <> 'EXCHANGE'
AND IND2.DEMAND_DATE >= convert(varchar,DATEADD(month,-3,DATEADD(DD, 1 - DAY(GETDATE()), GETDATE())), 105) AND IND2.DEMAND_DATE <= convert(varchar,DATEADD(month,-3,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0))), 105)
AND IND2.CUST_ID = IND.CUST_ID),0) AS QTY_ORDERED_THIS_PERIOD_1,
ISNULL((SELECT SUM(IND2.QTY_REQUESTED - IND2.QTY_BACKORDER) FROM
PS_IN_DEMAND IND2, PS_MASTER_ITEM_TBL IM2, PS_ORD_HEADER OH2
WHERE IND2.INV_ITEM_ID = IM2.INV_ITEM_ID
AND IND2.ORDER_NO = OH2.ORDER_NO
AND IM2.DESCR60 LIKE '%DRESSER%'
AND IM2.INV_PROD_FAM_CD = IM.INV_PROD_FAM_CD
AND OH2.CUSTOMER_PO <> 'EXCHANGE'
AND IND2.DEMAND_DATE BETWEEN DATEADD(month,-2,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0))) AND DATEADD(month,-2,DATEADD(DD, 1 - DAY(GETDATE()), GETDATE()))
AND IND2.CUST_ID = IND.CUST_ID),0) AS QTY_ORDERED_THIS_PERIOD_2,
ISNULL((SELECT SUM(IND2.QTY_REQUESTED - IND2.QTY_BACKORDER) FROM
PS_IN_DEMAND IND2, PS_MASTER_ITEM_TBL IM2, PS_ORD_HEADER OH2 WHERE
IND2.INV_ITEM_ID = IM2.INV_ITEM_ID
AND IND2.ORDER_NO = OH2.ORDER_NO
AND IM2.DESCR60 LIKE '%DRESSER%'
AND IM2.INV_PROD_FAM_CD = IM.INV_PROD_FAM_CD
AND OH2.CUSTOMER_PO <> 'EXCHANGE'
AND IND2.DEMAND_DATE BETWEEN DATEADD(month,-1,DATEADD(DD, 1 - DAY(GETDATE()), GETDATE())) and DATEADD(month,-1,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0)))
AND IND2.CUST_ID = IND.CUST_ID),0) AS QTY_ORDERED_THIS_PERIOD_3,
ISNULL((SELECT SUM(IND2.QTY_REQUESTED - IND2.QTY_BACKORDER) FROM
PS_IN_DEMAND IND2, PS_MASTER_ITEM_TBL IM2, PS_ORD_HEADER OH2 WHERE
IND2.INV_ITEM_ID = IM2.INV_ITEM_ID
AND IND2.ORDER_NO = OH2.ORDER_NO
AND IM2.DESCR60 LIKE '%DRESSER%'
AND IM2.INV_PROD_FAM_CD = IM.INV_PROD_FAM_CD
AND OH2.CUSTOMER_PO <> 'EXCHANGE'
AND IND2.DEMAND_DATE BETWEEN DATEADD(DD, 1 - DAY(GETDATE()), GETDATE()) AND GETDATE()
AND IND2.CUST_ID = IND.CUST_ID),0) AS QTY_ORDERED_THIS_PERIOD_4
FROM PS_IN_DEMAND IND
INNER JOIN PS_ORD_HEADER OH ON IND.ORDER_NO = OH.ORDER_NO
INNER JOIN PS_MASTER_ITEM_TBL IM ON IND.INV_ITEM_ID = IM.INV_ITEM_ID
INNER JOIN PS_TEAM_CODE_TBL TC ON OH.SUPPORT_TEAM_CD = TC.SUPPORT_TEAM_CD
AND TC.EFF_STATUS = 'A'
INNER JOIN PS_MEMBER_PERSON MP ON TC.SUPPORT_TEAM_CD = MP.SUPPORT_TEAM_MBR
AND TC.SETID = MP.SETID AND MP.EFF_STATUS = 'A'
AND MP.TEAM_MEMBER_TYPE = 'SALES'
AND MP.EXT_EMAIL_ADDR <> ''
LEFT OUTER JOIN PS_INV_ITEM_FAM IIF ON IM.INV_PROD_FAM_CD = IIF.INV_PROD_FAM_CD
AND IIF.EFF_STATUS = 'A'
AND IIF.EFFDT = (SELECT MAX(EFFDT) FROM
PS_INV_ITEM_FAM IIF2 WHERE
IIF2.INV_PROD_FAM_CD = IIF.INV_PROD_FAM_CD)
WHERE IND.IN_FULFILL_STATE IN ('50','60','70')
AND IND.DEMAND_SOURCE = 'OM'
AND IND.CUST_NAME NOT LIKE 'HARDEN%'
AND IND.SHIP_DTTM >= DATEADD(d,-180,getdate() )
AND IM.DESCR60 LIKE '%DRESSER%'
GROUP BY IND.CUST_ID, IND.CUST_NAME, IM.INV_PROD_FAM_CD, IND.IN_FULFILL_STATE, OH.SUPPORT_TEAM_CD, TC.DESCR, MP.EXT_EMAIL_ADDR, IIF.DESCR ORDER BY TC.DESCR, IND.CUST_NAME, IM.INV_PROD_FAM_CD
April 10, 2012 at 12:55 pm
Your BETWEEN will not work.
select DATEADD(month,-2,DATEADD(D, -1, DATEADD(M,DATEDIFF(M,0,GETDATE())+1,0))) , DATEADD(month,-2,DATEADD(DD, 1 - DAY(GETDATE()), GETDATE()))
Results:
2012-02-29 00:00:00.000 2012-02-01 12:53:59.663
The first date is greater than the second.
April 10, 2012 at 12:58 pm
Try replacing your date filter with this:
AND (IND2.DEMAND_DATE >= dateadd(mm, datediff(mm, 0, getdate()) - 2, 0) AND IND2.DEMAND_DATE < dateadd(mm, datediff(mm, 0, getdate()) - 1, 0))
April 10, 2012 at 1:04 pm
That worked thanks
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply