• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

Sql Query to return results for current month

Hi All I have the following query

SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, 
                      dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime) 
                      END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm, 
                      dbo.itran.it_exdate) AS Month_No, dbo.ihead.ih_credit AS Credit
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal LIKE '%LINITSER%') AND 
                      (dbo.ihead.ih_credit NOT LIKE '%CRE%')
ORDER BY our_date

Open in new window


What I would like to do is only return results where Our_Date  = Current month

Can anybody help.

John
0
pepps11976
Asked:
pepps11976
1 Solution
 
BurundiLappCommented:
These any use to you, these are the SQL reference codes I use in various queries relating to dates:

SELECT 
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-3,0)) AS LD_PQ, -- last second of last day of previous quarter
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-2,0)) AS LD_THREEM, -- last second of last day of 3 months ago
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)) AS LD_TWOM, -- last second of last day of 2 months ago
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) AS LD_LASTM, -- last second of last day of last month
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0)) AS LD_THISM, -- last second of last day of this month
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+2,0)) AS LD_NEXTM, -- last second of last day of next month
DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+3,0)) AS LD_TWOMH -- last second of last day of 2 months hence

SELECT
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-3,0)) AS FD_LASTQ, --FirstDayof Last Quarter
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-2,0)) AS FD_TWOMA, --FirstDay of two months ago
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())-1,0)) AS FD_LASTM, --FirstDay of previous month
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate()),0))   AS FD_THISM, --FirstDay of current Month
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+1,0)) AS FD_NEXTM, --FirstDay of Next Month
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+2,0)) AS FD_TWOMH, --FirstDay in two months time
DATEADD(s,0,DATEADD(mm, DATEDIFF(m,0,getdate())+3,0)) AS FD_THREEMH --FirstDay in three months time

Open in new window

0
 
Surendra NathCommented:
try this
SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, 
                      dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime) 
                      END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm, 
                      dbo.itran.it_exdate) AS Month_No, dbo.ihead.ih_credit AS Credit
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal LIKE '%LINITSER%') AND 
                      (dbo.ihead.ih_credit NOT LIKE '%CRE%')
AND     month(CAST(dbo.itran.it_due AS datetime)) = month(getdate()) 
and     year(CAST(dbo.itran.it_due AS datetime)) = year(getdate()) 
ORDER BY our_date

Open in new window

0
 
pepps11976Author Commented:
Neo_jarvis

I get an error with the code you provided saying The conversion of a date data type to a datetime data type resulted in an out of range value
0
 
mensmaximusCommented:
Did you try to expand your where clause by adding the date criteria for our_date? I would try adding something like:

and datepart(yy,our_date) = datepart(yy,dateadd(m,0,getdate()))
and datepart(m,our_date) = datepart(m,dateadd(m,0,getdate()))

Open in new window

0
 
Surendra NathCommented:
Ok,  I got it the data in your table for the column itran.it_due  might have some values which cannot be converted to datetime, no worries, the below code might handle it.

;WITH CTE AS
(
SELECT     TOP (100) PERCENT CAST(dbo.itran.it_exdate AS datetime) AS Month, dbo.ihead.ih_sorder AS Sales_Order, dbo.ihead.ih_doc, dbo.ihead.ih_sprojid AS Opportunity_No, 
                      dbo.ihead.ih_custref AS Customer_Reference, dbo.itran.it_stock AS Stock_Reference, dbo.itran.it_quan AS Quantity, dbo.ihead.ih_account AS Account, 
                      dbo.ihead.ih_name AS Name, CAST(dbo.itran.it_exdate AS datetime) AS Customer_Date, CAST(dbo.itran.it_due AS datetime) AS Our_Date, CAST(DATEDIFF(dd, 
                      dbo.itran.it_exdate, dbo.itran.it_due) AS varchar(20)) AS Date_Difference, CAST(DATEDIFF(dd, dbo.itran.it_exdate, { fn NOW() }) AS varchar(20)) AS Past_Due, 
                      dbo.itran.it_doc AS [Document], dbo.ihead.ih_quotat AS Quotation, dbo.ihead.ih_proform AS Proforma, dbo.itran.it_anal AS Anaylysis_Code, 
                      CAST(dbo.ihead.ih_orddate AS datetime) AS Order_Date, CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' THEN NULL ELSE CAST(dbo.itran.it_dtedelv AS datetime) 
                      END AS Delivery_Date, CASE WHEN dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN 'Pass' ELSE NULL END AS Passed_Delivery, 
                      CASE WHEN dbo.itran.it_dtedelv = '1899-12-30' OR
                      dbo.itran.it_dtedelv BETWEEN DATEADD(DD, - 5, it_exdate) AND it_exdate THEN NULLIF (' ', ' ') ELSE 'Fail' END AS Failed_Delivery, DATEPART(mm, 
                      dbo.itran.it_exdate) AS Month_No, dbo.ihead.ih_credit AS Credit
FROM         dbo.ihead LEFT OUTER JOIN
                      dbo.itran ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.ihead.ih_orddate >= '2009-01-01') AND (dbo.itran.it_anal NOT LIKE '%zz%') AND (dbo.itran.it_anal LIKE '%LINITSER%') AND 
                      (dbo.ihead.ih_credit NOT LIKE '%CRE%')
ORDER BY our_date
)
SELECT * FROM CTE 
WHERE   month(CAST(our_date AS datetime)) = month(getdate()) 
and     year(CAST(our_date AS datetime)) = year(getdate()) 

Open in new window

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now